Last week I asked readers to create a simple invoice application using the specifications I listed. See: Your Assignment: Create A Simple Invoice App.
Nine people (including me) responded:
- Kirk Anderson
- Per Arnader
- Fadi Chalohi
- Ken Clifton
- Donald R. Cossitt
- Doug Glancy
- Aurelio L’Ambrosa
- John Walkenbach
- Charlene Wright
You can download all nine workbooks (zip file). I changed the workbook names so they correspond to the author names. Some of the files contain macros, and they are all virus-free.
As you might suspect, there were lots of different approaches — ranging from very simple to very complex. I think we can all learn a few things by studying these apps.
I invite everyone who responded to leave a comment here with a brief description of how you approached the problem.
Here’s how I approached it:
I used Data Validation to allow the user to select the products from a list and specify the quantity. I used a macro to jump to the Quantity column after an item is entered. Another macro jumps back to the Item column when a Quantity is entered. This isn’t necessary, but I thought it was a nice touch.
Unit costs are obtained using a VLOOKUP formula. I also included a checkbox (labeled “Allow item & price overrides”), linked to a simple macro. When checked, the user can enter a product that’s not on the list, and also overwrite the VLOOKUP formula with a different price. A button at the top of the form clears the invoice, and also regenerates the formulas.
In order to determine whether sales tax should be added, I used separate cells for the city, state, and zip. I used a Data Validation Input Message to help identify these cells. Using separate cells for the address (at the top) required that I use merged cells down below.
This app is very simple, but it meets all of the original requirements. And I’m positive that it would take less than five minutes for a user to get up to speed.
Thanks to all who participated. Hopefully, this will generate some good discussion.