auto-incrementing reference in word
I have one word installation, everytime I click new, I want the template in use to generate a four digit (starting with 000) reference on the document, probably footer?
Any ideas? VB? Macro wont cut it, must be automatic ideally when the user clicks on new
Greg K posted this at 13:23 — 24th October 2007.
He has: 2,145 posts
Joined: Nov 2003
Not sure, but here are some considerations.
I click New, it generates 0123 for that document. I change my mind and don't save it, just close it. Next time I hit new are you wanting it to be 0124 this time or still use the 0123 since it was never saved?
Just some thought to put into the problem for anyone deciding to try to make this work.
-Greg
mfdc posted this at 13:28 — 24th October 2007.
They have: 160 posts
Joined: Aug 2004
Thanks guys, Ive managed to cobble together some great VB that does it, wasnt really sure about where to store the variable, I decided against placing it in the registry, and thus not tying the template to one machine, Ive stored it in a text file in the root of C. When the user clicks new, the program code searches the text file, and adds one to whatever numbers there, and displays it in the document at a position that I specified. The text file is updated and thus, everytime the user creates a new document, he gets his incrementing number. Not very tight I know, but works a treat
Greg K posted this at 18:16 — 7th November 2007.
He has: 2,145 posts
Joined: Nov 2003
Funny, about an hour ago our Office Manager called me to ask me how to do this in Excel for Purchase Orders.
So I went looking and here is what I found, which I'm sure could be used in word as well with some modification. This handles the issue of if they do not save the file, and stores the value in your registry.
http://en.allexperts.com/q/Excel-1059/Auto-number-generation-1.htm
Here is the version I put in the file (use ALT-F11 to open VBA editor), I will explain the features below it:
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mpFile As String
Dim mpValue As Long
Static mpReentry As Boolean
Dim tmpFile As String
If Not mpReentry Then
mpReentry = True
Application.EnableEvents = False
Cancel = True
mpValue = GetSetting("IBP", "PurcahseOrders", "CurNum", 1000) + 1
If ThisWorkbook.Name = "PurchOrder.xls" Then
' This is the default name
If MsgBox("Do you wish to save Purcahse Order #" & ActiveSheet.Range("E4").Value & "?", vbYesNo, "Save File") = vbYes Then
tmpFile = Application.DefaultFilePath & "\PurchOrder_" & mpValue - 1 & ".xls"
ActiveWorkbook.SaveAs tmpFile
SaveSetting "IBP", "PurcahseOrders", "CurNum", mpValue
End If
End If
Application.EnableEvents = True
ThisWorkbook.Close savechanges:=False
mpReentry = False
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim mpValue As Long
If ThisWorkbook.Name = "PurchOrder.xls" Then
Application.EnableEvents = False
Cancel = True
MsgBox "Due to the built in numbers, you have to close this file to name/save it.", vbOKOnly, "Save File"
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_Open()
ActiveSheet.Range("E4").Value = GetSetting("IBP", "PurcahseOrders", "CurNum", 1000)
End Sub
At this point it, you do not want it to be saved back out as PurchOrder.xls, as you want to keep that file safe. So if you hit save, it lets you know you have to close to save the file, at which point it auto saves to the same directory as the PurchOrder.xls that you opened, and names it PurchOrder_#####.xls It then updates the registry with the new current value
Now if you need to go back and edit the PO later, you can open it and do a save, as the filename is not PurchOrder.xls Only when you open the base one does it auto number.
If you need to adjust the number that it is currently at, you can open the registry editor and the value will be at :[INDENT]HKEY_Current User[INDENT]Software[INDENT]VB and VBA Program Settings[INDENT]IBP (first value in the SaveSetting/GetSetting from code)[INDENT]PurchaseOrders (second value in the SaveSetting/GetSetting from code)[/INDENT][/INDENT][/INDENT][/INDENT][/INDENT]Also as an alternative for the REGEDIT shy, you could just create a simple spreadsheet where you type in a value on A1 and then set the code to be:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
SaveSetting "IBP", "PurcahseOrders", "CurNum", ActiveSheet.Range("A1").Value
End Sub
-Greg
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.