Custom formatting numbers & dates with a template (for receipts/invoices)

  • Thread starter Thread starter Jude
  • Start date Start date
J

Jude

I have a template that I use to make receipts. One of the cells holds
the information that I would like to change every time I open the
template. For instance, the first order in Jan. 2004 looks like this:
1-J04
The second order in Jan. 2004 looks like this:
2-J04
First order, Feb. 04:
1-F04
And so on. I can't seem to figure out how to do this & would really
appreciate help! TIA. -Jude
 
Public Sub Auto_Open()
Dim iPos As Long
Dim iVal As Long

With Worksheets("Sheet1").Range("A1")
If Right(.Value, 3) = Format(Date, "mmm") Then
iPos = InStr(1, .Value, "-")
iVal = Left(.Value, iPos - 1) + 1
.Value = iVal & "-" & Format(Date, "mmm")
Else
.Value = "1-" & Format(Date, "mmm")
End If
End With
End Sub

put it in a standard code module

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top