Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Templates - automatic numbering
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Templates - automatic numbering
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) "john" wrote in message ps.com... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Templates - automatic numbering
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. "john" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel automatic numbering | Excel Discussion (Misc queries) | |||
How do I set up automatic sequentially numbering for worksheets? | Excel Worksheet Functions | |||
Automatic Numbering Upwards | Excel Discussion (Misc queries) | |||
How do I set up invoice automatic numbering in excel? | Excel Discussion (Misc queries) | |||
automatic sequential numbering in excel or word | Excel Discussion (Misc queries) |