Save Functions‎ > ‎

AutoNumbering a Template

                                    Sample File:  Invoice.xlt
"Each time I use my 'invoice' template, I'd like to generate a new sequential invoice number, not skipping any numbers and not using any external workbooks to reference for numbers. Can that be done?"

  1. Operates off of normal Excel template files
  2. The invoice number increment each time an invoice is saved for the first time
  3. Numbers should never increment on a saved invoice ever again, nor affect the numbers of any other invoices based on the same template
  4. It should be automatic
  5. You will have to turn off macros each time before you save your original invoice template to ready it for use, else the macro we are putting in would cause an error. This macro is only designed to run on workbooks created FROM the template.
  6. Printing is not allowed until the invoice is saved at least once. (A separate macro to prevent that...)
METHODOLOGY:   When the new workbook is saved for the first time it will
  1. Check and see if a permanent invoice number has been assigned by checking cell AA1.
  2. If not numbered yet, it will secretly REOPEN the original template file and increment the number in the template itself by one, and save the template again
  3. It will remember the new number and put that number into the new workbook permanently
  4. ...marking cell AA1 as "incremented" thus turning off these automacros in the future.
  5. Continue with a normal SaveAs from that point on.
This macro goes into the ThisWorkbook module of your new Invoice.xlt template file.  You will need to know the full path to where this template is stored because the template has to be reopened during the SaveAs function.


Option Explicit
Const wsINV As String = "Sheet1"
'Jerry Beaucaire,  9/4/2011
'Self-enclosed incrementing invoice numbers
'from a template, multi-user compatible

Private Sub Workbook_BeforeSave _
    (ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim NewVal As Long

With Sheets(wsINV)               'the sheet with the invoice number on it
    If .Range("AA1") = "" Then      'the doublecheck cell
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
    'reopen the template
        Workbooks.Open Filename:= _
            "C:\Documents and Settings\Jerry\My Documents\Excel Tips\Samples\Invoice.xlt", Editable:=True
        With ActiveWorkbook.Sheets(wsINV)    'update the template's current inv number
            .Range("B2").Value = .Range("B2").Value + 1
            NewVal = .Range("B2").Value         'remember the new number
        End With
        ActiveWorkbook.Close True               'close the template, save changes
        .Range("B2").Value = NewVal             'put new number into current workbook
        .Range("AA1") = "Incremented"           'flag the doublecheck cell
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End With

        'normal save continues from here...
End Sub

'This next macro will keep the user from trying to print
'the invoice before it receives a permanent inv number

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If Sheets(wsINV).Range("AA1") = "" Then
        MsgBox "You must save the workbook before printing is allowed."
        Cancel = True
    End If
End Sub

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!