How to create auto numbering invoice templates

%3CLINGO-SUB%20id%3D%22lingo-sub-1654052%22%20slang%3D%22en-US%22%3EHow%20to%20create%20auto%20numbering%20invoice%20templates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1654052%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20to%20create%20an%20excel%20template%20for%20a%20client.%20I%20need%20it%20to%20automatically%20generate%20a%20new%20invoice%20number%20each%20time%20my%20client%20opens%20the%20document.%20The%20numbers%20need%20to%20be%20sequential%20and%20I%20would%20like%20to%20define%20the%20starting%20number.%20I%20have%20tried%20using%20this%20visual%20basic%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3ERange(%E2%80%9CC2%E2%80%9D).Value%20%3D%20Range(%E2%80%9CC2%E2%80%9D).Value%20%2B%201%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20C2%20is%20the%20cell%20the%20number%20needs%20to%20be%20automatically%20generated%20to%20but%20I%20keep%20getting%20a%20runtime%20error%20(see%20file%20here).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%3F%20I%20am%20using%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1654052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1654125%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20auto%20numbering%20invoice%20templates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1654125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F789219%22%20target%3D%22_blank%22%3E%40Jane_Chameleon%3C%2FA%3E%26nbsp%3BI%20think%20you're%20getting%20an%20error%20because%20you%20have%20an%20undefined%20sheet%20for%20that%20range.%26nbsp%3B%20Try%20defining%20the%20sheet%20before%20that%20RANGE%20function.%3C%2FP%3E%3CP%3EThat%20said%2C%20your%20technique%20has%20a%20number%20of%20drawbacks%20including%20if%20they%20open%20it%20up%20and%20close%20it%20without%20'creating'%20a%20new%20invoice%20the%20number%20increments%2C%20if%20they%20open%20an%20old%20invoice%20to%20look%20at%20it%20the%20number%20increments%2C%20if%20they%20open%20the%20template%20and%20create%20and%20print%20a%20new%20invoice%20but%20don't%20save%20it%20(or%20it%20fails%20to%20save)%20then%20the%20increment%20doesn't%20save%20and%20you%20get%20duplicate%20invoice%20numbers.%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20system%20for%20POs%20and%20such%20but%20I%20base%20it%20off%20a%20LOG.%26nbsp%3B%20My%20LOG%20has%20a%20button%20to%20create%20a%20new%20item%20that%20will%20identify%20the%20new%20number%26nbsp%3B%20and%20pop%20up%20a%20form%20with%20basic%20info%20to%20populate%20the%20LOG.%26nbsp%3B%20That%20way%20the%20LOG%20will%20show%20each%20number%20and%20relevant%20info%20and%20then%20it%20will%20also%20open%20the%20template%20and%20pre-populate%20those%20values%20into%20the%20sheet%20for%20the%20user%20and%20even%20saves%20that%20new%20sheet%20in%20a%20given%20location%20with%20a%20fixed%20naming%20style%20so%20the%20folder%20shows%20all%20the%20items%20in%20a%20unified%20way.%20-%20works%20really%20well%2C%20but%20that%20is%20just%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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).

 

Can anyone help? I am using Office 365.

 

Thank you in advance...

 

1 Reply

@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.