Auto number for an invoice

I have also been looking for a way of assigning the next consecutive reference number to a worksheet.

If you split the reference information/number into a seperate file that is saved on a commonly accessible drive (fileserver), then the mechanics of the function changes, eliminating some of the inherent problems associated with using only one template (or worksheet) file to achieve this function.

I'm not sure if this is going to meet your requirements, however it seems to be working ok for us. It also means that the file containing the next number to be used, is only ever accessed for the duration of running this code (very briefly), and the likelyhood of more than one user trying to access the reference file is virtually eliminated. (The procedure will crash if the reference file is in use, however I haven't found a tidy way of managing this exception.)

Let me know if this works ok for you...

Private Sub CommandButton1_Click()
Dim newnum As Integer
Application.ScreenUpdating = False
ActiveSheet.unprotect

'Open shared reference file with last used reference number:
Workbooks.Open Filename:="\\fileserver\master_reference.xls", editable:=True

newnum = Workbooks("Master_Reference.xls").Sheets("Sheet1").Range("MasterReference").Value
newnum = newnum + 1
'Update master with incremented ref number:
Workbooks("Master_Reference.xls").Sheets("Sheet1").Range("MasterReference").Value = newnum

ActiveWorkbook.Close savechanges:=True
'Update working document with assigned ref number:
Range("UpdateMe").Value = newnum

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

Johan,

You can use this. This assumes that cell "AA1" is not used on the invoice. It will keep a running log of the last number used and fill in the invoice number for you when the file is opened. This goes in the "ThisWorkBook" module in the Microsoft Excel Objects folder. You will have to fill in the cell address where the invoice number is to be entered where it says "PUT CELL RANGE HERE". Let me know how this works for you. Also you will have to go to File --> Open and Find the Invoice template and open it to put this code in and then save the template with this code. Good Luck,

Ryan

Private Sub Workbook_Open()
If Range("AA1").Value <> "" Then
Range("AA1").Value = Range("AA1").Value + 1
Range("PUT CELL RANGE HERE").Value = Range("AA1").Value
Else
Range("AA1").Value = 1
Range("PUT CELL RANGE HERE").Value = Range("AA1").Value
End If
End Sub

Ryan
Assuming that the cell “PUT YOUR RANGE HERE” is B5, can the code not be reduced to :-

Private Sub Workbook_Open()
Range("AA1").Value = Range("AA1").Value + 1
Range("B5").Value = Range("AA1").Value
End Sub

Or even :-

Private Sub Workbook_Open()
Range("B5").Value = Range("B5").Value + 1
End Sub

However, this subject was discussed on this board in March (Numbering a Worksheet - posted by Michaela on March 02, 19100 at 13:41:58)
At that time, it was not completely resolved and the problems raised then would appear to apply also to your suggested solution as follows :-

Whatever code is used, if the file is a template, the first time it is opened it will show Inv. No.1, and will show the same number every time it is opened.

Also, if the workbook opened from the template is saved as an .xls file, every time this .xls file is opened the Inv. No. will be increased by +1.

This is the reverse of what should happen.

Any suggestions?

Celia