Autoincrementing cell in Template

  • Thread starter Thread starter Andy Rabin
  • Start date Start date
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
 
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
 
Back
Top