Excel is commonly used to generate invoices for small businesses. Getting Excel to generate automatically incrementing invoice numbers requires digging into the Excel Visual Basic editor; this is often the very first macro a novice Excel user makes.
Create an invoice in Excel, and save the file as "Invoice.xls"
Enter your initial invoice number in cell A1. For example, if your initial invoice number is 300, you'd type 300 in cell A1.
Press the "Alt" and "F11" keys at the same time. This will open the Visual Basic editor.
Within the Visual Basic editor, press "Ctrl"+"R" to open the Project Explorer window.
Double click the line that says VBA(Invoice.xls). When the menu opens up, double-click "ThisWorkbook," which is a special function in Visual Basic detailing how operations on this workbook will operate. A pane will show up on the right.
Enter the following text, exactly as shown here, in the pane on the right, without the quotes. The line breaks are important -- this should show up on three lines in the window.
"Private Sub Workbook_Open()
Range("a1").Value = Range("a1").Value + 1
Save and reopen the file. Every time the Invoice.xls file is opened, the number in cell A1 will have 1 added to it.
If you're doing this in Excel 2007, the file name will be Invoice.xlsx, and should be referenced as Invoice.xlsx in Step 5 above.
Tips and warnings
- If you're doing this in Excel 2007, the file name will be Invoice.xlsx, and should be referenced as Invoice.xlsx in Step 5 above.