Autoincrementing cell in Template

A

Andy Rabin

I am currently creating a template. My final stumbling block is creating
an auto incrementing cell in the template. What I want to happen is that
every time the template is opened, the next sequential number fills in the
cell. The format I am looking for is basically yy-#####. For example,
the first time it is used this year, it would assign the cell to 04-00001,
followed by 04-00002, etc. I am lost on how to do this. Any ideas?

Thanks,
Andy
 
F

Frank Kabel

Hi
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)

Private Sub Workbook_Open()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = Format(date,"YY") & "-" & format(regValue,"00000")
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
End With
End Sub
 
Top