Automatic invoice numbering

[kenny9002]

Please I need help with a vba code for automatic invoice numbering.

We have an Excel invoice template used for generating invoices. In the past, the invoice numbering has been done manually and this has led to the problem of missing out some number ranges and also having more than one invoice with the same number.

We want to ensure that when future invoices are generated, they are numbered automatically and sequentially. Currently, for each invoice, the invoice number is typed into Cell B12, and the current number is S1204. We want the automatic and sequential numbering to start from S1205, with the number inserted into the appropriate cell i.e. Cell B12.

A vba code that will help us achieve this will be greatly appreciated.

Thank you.

Kenny

 

[njimack]

Hi, maybe something like this...

Sub Inv_No()
Dim Inv As Long
With Range("B12")
    Inv = Right(.Value, Len(.Value) - 1) + 1
    .Value = Left(.Value, 1) & Inv
End With
    
End Sub

 

[kenny9002]

Thank you Neil for your kind response.

I have a few questions and don't know if you or any of our other experts on the forum could help.

1. Does it matter that we want the automatic and sequential invoice numbering to start at S1205?

2.We would not want to create invoice numbers that will never be used. Therefore, what happens if someone opens the invoice template, enters a few details and then CHANGES his/her mind and closes the template without going ahead to generate the invoice?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
3.Is it possible for the code to achieve the following:
<o></o>
(a) save a copy of the invoice with the Invoice Number as the name of the file and then clear the entry data to get ready for the next invoice; and
<o></o>
(b) When the workbook is closed, the invoice is cleared and saved as the Master Invoice.
<o></o>
Thank you.

Kenny

 

[kenny9002]

Please can the Experts on the forum help with my Post #3.

Thanks in anticipation.

Kenny

 

[kenny9002]

Hi All,

I refer to my Posts #1 and #3 respectively, and still ask for help from the Experts on the forum.

I have searched around and looked at some of the solutions suggested on the subject. However, none of the suggested solutions yet takes into consideration some important issues that might arise in automatic invoice numbering as follows: There are issues such as 'What if the file is opened and closed again without producing an invoice? & 'What if the invoice is printed twice?' I believe that these are some very important considerations in designing macros for automatic invoice numbering.

I believe that in order to overcome some of those problems, the routine of entering invoices should be to open the Master Invoice; Enter and save as many invoices as needed; Close the workbook and save as Master Invoice, over-riding the original Master Invoice. This will save the latest invoice sequential number.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This means that there should be THREE buttons on the Invoice Sheet as follows:<o></o>
<o></o>
1. New Customer button - used to assign Invoice Number<o></o>
2. Save Invoice button - saves a copy of the invoice with the Invoice Number as the name of the file and then CLEARS the entry data to get ready for the next invoice.<o></o>
3. Close Workbook button - clears the Invoice and saves the workbook as the Master Invoice.<o></o>
<o></o>
Please, I am therefore seeking your help with vba codes for the three buttons that I need to place on the Master Invoice - with the ideas stated above - to help us to generate invoices with automatic & sequential numbering.

Thanks for your anticipated help.
<o></o>
Kenny<o></o>