MAKING EXCEL WORK FOR YOU
Basic Invoice for Excel - auto-numbering
This Excel Invoice Template based on Excel, provides a fill In the blank invoice form and is capable of creating and calculating invoices. The template shows quantity, description, unit price, taxable status, and amount per line. Sub-total, tax and total amount are computed from the line items automatically. With the intuitive user interface, creating invoices is as easy as filling a form.
There are many such invoice templates available and it’s relatively easy to create your own. However, these templates are a great starting point, but one major shortfall is that they won't automatically generate unique invoice numbers—you'll need a macro for that. This invoice is offered not specifically for you to use, although it is fully functional, but to demonstrate how to write the code to generate unique numbers for your invoices.
The Excel Invoice Workbook consist of a template worksheet which you can change as much as you want, add your business details, change the colours, etc. However, the cell I7 must contain the invoice number or else the code will not work properly. You can use a different cell but you must remember to change the code accordingly. The template sheet has a shape with the first macro attached to it which acts as a button to run the code.
The Macro Code
If you've never used macros before it would be a good idea to read this.
Macro #1: Creates the a new sheet and generate the next invoice number. When the new sheet is created the code also deletes the add new sheet button as it will not be needed on this sheet. At the same time it makes a different button visible. This button runs a macro to save the invoice to a new workbook.
Macro #2 provides an option to save the new sheet as a separate workbook. The code requires a separate folder named “Invoice Archive” to be held within the folder that contains the Invoice workbook. When used the macro saves and renames the new invoice to a new workbook in this folder. The button is removed as it will no longer be needed and the code will not work in the new workbook.
Click to view the code
As the name implies this example although usable is extremely basic. A true invoicing program should have databases for customers, invoice records, sales data. In addition it should be possible to email the invoice to your customers as a pdf file. It is also desirable to be able to produce quotes and credit notes.
I have produced many bespoke invoice programs for clients over the years and would be pleased to quote for such work.
I will soon be making available an advanced Invoice program in Excel. Watch for more details
© Roy Cox 2005 to 2013
|The Personal WorkBook|
|Excel Sheets in VBA|
|Excel Macro Recorder|
|Excel Camera Tool|
|Excel Summarise Data|
|Excel CHOOSE Function|