Greg K — Wed, 07 Nov 2007 18:16:16 +0000

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

'The "template" which isn't actually a template, but a base file is PurchOrder.xls When you open it, it gets the next # from the registry and places it in cell E4

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

'Anyhow, thought I would share this here for others.

-Greg

mfdc — Wed, 24 Oct 2007 13:28:49 +0000

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 — Wed, 24 Oct 2007 13:23:36 +0000

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