DH Templates

how to assign an auto number for excel invoice template 2003?

Public Comments

1. My approach to this has always been to use a 'date/time' based unique number for invoice number generation, using a WorkBook_Open event handler. Using this, one can tell the year, month, and day the invoice was created just by examining the invoice number itself. Since most, if not all, businesses retain invoice file copies for tax/customer service purposes this identifies the file location. The following event handler will create a unique number every time the template is opened, in the format: yy-mmddhhmmss Copy the event handler to the clipboard: Private Sub Workbook_Open() If Range("A1").Value = "" Then If Len(Month(Date)) = 1 Then mnth = "0" & Month(Date) Else mnth = Month(Date) End If If Len(Day(Date)) = 1 Then dy = "0" & Day(Date) Else dy = Day(Date) End If If Len(Hour(Time)) = 1 Then hr = "0" & Hour(Time) Else hr = Hour(Time) End If If Len(Minute(Time)) = 1 Then mn = "0" & Minute(Time) Else mn = Minute(Time) End If If Len(Second(Time)) = 1 Then sec = "0" & Second(Time) Else sec = Second(Time) End If Range("A1").Value = Right(Year(Date), 2) & "-" & _ mnth & dy & hr & mn & sec Columns("A:A").AutoFit End If End Sub Open the template and press ALT + F11 Double click 'THIS WORKBOOK' in the Microsoft Excel Objects in the upper left quadrant. Paste the code into the editing area to the right. Close the VBE and return to the worksheet. Save the workbook as your template, '.xlt'.