I am struggling to create an excel template for a client. I need it to automatically generate a new invoice number each time my client opens the document. The numbers need to be sequential and I would like to define the starting number. I have tried using this visual basic:
Private Sub Workbook_Open() Range(“C2”).Value = Range(“C2”).Value + 1 End Sub
Where C2 is the cell the number needs to be automatically generated to but I keep getting a runtime error (see file here).
@Jane_Chameleon I think you're getting an error because you have an undefined sheet for that range. Try defining the sheet before that RANGE function.
That said, your technique has a number of drawbacks including if they open it up and close it without 'creating' a new invoice the number increments, if they open an old invoice to look at it the number increments, if they open the template and create and print a new invoice but don't save it (or it fails to save) then the increment doesn't save and you get duplicate invoice numbers.
I have a similar system for POs and such but I base it off a LOG. My LOG has a button to create a new item that will identify the new number and pop up a form with basic info to populate the LOG. That way the LOG will show each number and relevant info and then it will also open the template and pre-populate those values into the sheet for the user and even saves that new sheet in a given location with a fixed naming style so the folder shows all the items in a unified way. - works really well, but that is just me.