Templates - automatic numbering

[john]

I have a template that I want a unique, sequential number
to appear every time the file is opened. Meaning, the first time it is

opened the number could be 101. The next time the template is used,
the file should open with 102 in the designated field and so on.

Any thoughts on how to accomplish this?

 

[Bob Phillips]

Here is one way


Private Const kBaseName As String = "myFile"
Private Const kName As String = "__RefNum__"


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim iPos As Long
On Error GoTo CleanUp
Application.EnableEvents = False


If IsError(Evaluate(kName)) Then
Me.Names.Add Name:=kName, RefersTo:=1
Else
Me.Names.Add Name:=kName, RefersTo:=Evaluate(kName) + 1
End If


Me.SaveAs Filename:=kBaseName & "_ref_" &
Format(Evaluate(Me.Names(kName).RefersTo), "000") & ".xls"
Cancel = False


CleanUp:
Application.EnableEvents = True
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

You can use the event Workbook_open, and use a macro like this:
Private Sub Workbook_Open()
Worksheets("sheet1").Range("A1").Value =
Worksheets("sheet1").Range("A1").Value + 1
End Sub

The code must be placed in the workbook code area, that can be accessed by
right click on the Excel icon left to the file menu and selecting "View code"

Hope this helps,
Miguel.