31 thoughts on “Invoice App: The Results”

1. I figured you’d all go for macros so I thought of a non-macro approach.

   I used Data Validation for the product selection while allowing override. To make sure that the prices would display correctly in the case of a manual over-ride, I used “Paste Picture Link” into the prices column.

   I also used Data Validation for the selection of the states which would in turn apply a sales tax.

   Finally, an additional non-printing column displays error messages (no quantity, no price, etc…).

   I was tempted to fix the Scrolling Area through a macro but I thought I’d stick to non-macros and use the Window Freeze to control the viewing area.

   I could have done more on the protection side but I’ll blame it on time.

   The template is from Microsoft’s Template Gallery

2. [hope this doesn’t post twice, it didn’t seem to work the first time]

   Hi All,

   I figured the best way to make the workbook bulletproof was to keep the user inside modal userforms. This way I have control over everything the user is able to click on. Assuming macros are enabled, the first thing the user sees is a userform, and the only way out is via a manager password (jm40gPl4). If the user has this password, they are dropped into a completely unprotected workbook. Without the password, though, all they can get to is an invoice form, or an exit button that closes Excel without saving changes.

   The product list is sorted every time the workbook opens; so if the manager adds products to the list, it will be sorted again the next time the workbook is opened.

   The invoice userform gives fields for the customer name and address, with a drop-down for the state abbreviation. Then it gives an Items frame for the user to select the items that should be on the invoice. I wanted to present this list of items in a grid, but only wanted to use intrinsic controls. I couldn’t figure out how I would make ListBox work for this (I think I can learn something from Aurelio’s Select Item form, although I don’t think a user could add items into that control), so I settled for a one-item-at-a-time view.

   The user can type in an item description that is not on the list. They can also modify the quantity and price for any item. The user can add additional items by changing the item number above the current number of items, and can remove an item by clicking the Remove button while that item is shown. They will be prompted to make sure that’s really what they want to do.

   The user has a Print button which uses a Print Form worksheet to lay out the data. I didn’t spend as much time laying out the actual invoice as everyone else did — I ran out of time. It’s very simple, without even the current date. I would have designed it better if I’d had more time, but it still probably wouldn’t look as good as most of the other submissions. It does handle multiple pages in case the user enters more items than will fit on the page. The tax calculation is more hard-coded than I generally would do, but again I focused most of my time on the user interface, and threw in the invoice itself last-minute.

   I originally pictured the invoice as another userform, but when I got ready to put it together, I didn’t find a PrintOut method on the UserForm object. So I settled for building a print worksheet instead.

   There may be a potential quantity mismatch problem with the right (wrong?) steps. New items are allocated in memory with quantity 0, and the code to change the control to 1 was added sorta late, so it may be possible to have the control show 1 for an item whose internal qty is actually 0. This needs testing.

   Memory allocated for items is not de-allocated when items are removed, which means some memory is wasted when an item is removed. Adding a new item does use already-allocated memory though, if there is some, so the effect should be minimal. All item memory is freed when the invoice form is cleared.

   I forgot to disable the top-right X on the password form, but it seems harmless because it just goes back to the Main form anyway.

   Originally I had a Product List button where the Manager Mode button is. The way into Manager Mode was going to be obscured somewhere else, and Product List would have allowed editing the list in a userform. I decided creating the userform for Product List was going to take too much time, and decided the Password userform was secure enough that I didn’t need to obscure the path to find it.

   Another feature I’d like to add if I had more time would be a Preferences tab the manager could use to change certain things, such as the manager password, business name/address, etc.

   This comment is probably too long already, so I’ll shut up now. :)

   Thank you,

   Charlene Wright
   charmtg at yahoo dot com

3. Some really great work! I’m really impressed with all of them.

   I think in terms of speed, Kirk’s solution would be my favourite. To me, the listbox allows me to quickly find what product I’m looking for.

   Though style points to Aurelio and Fadi.

   John: What is that yellow box that appears when you click on State – and how do I make one?

   Rob

4. Rob, that yellow box results from data validation. It’s the “input message”. The cells don’t really use data validation. I’m just taking advantage of the ability to display a message when the cell is selected.

5. The variations on answering this question are very informative from an Excel perspective. Thanks!

   But, my nit –
   I live in an area with overlay area codes so ALL numbers require a ten-digit “dial”. The format is xxx-xxx-xxxx not (xxx)xxx-xxxx. Get it right!

6. The main thing I did different than most here was using a separate worksheet for the data entry and for the invoice template. One reason was to be able to create a multi-page invoice by inserting the data into the template before printing. My print button takes you to Print Preview, so you can see the finished product before printing.

   I tried to keep the user interface as spare as possible. There is no “Help.” Instead I tried to guide the user with data validation, or shut down options that weren’t appropriate, e.g., the print button is only enabled when all cells are filled, or used conditional formatting, e.g., duplicate entries both turn red.

   I also limited their options, in that they had to enter at least part of a row before they could start another. This could be frustrating, I think, but I thought it might help keep the novice user with only 5 minutes training out of trouble.

   The required ability to enter products not on the list was interesting to code. I let them change their mind, i.e., if they enter a new product they can give it whatever price they want, but if they then click back to a listed product in the same cell, the correct price for that product is inserted. This took me a while to get right.

   The last line of code I did last night introduced a bug which I didn’t catch because I still had “on error” turned on. I’ve fixed it now so that adding a new row selects that row and allows you to tab out of the selected cell – for some reason you have to click out in the version here. Now that it’s fixed I’m thinking of starting my own vitamin company!

   It was great to look at the other submissions, some very nice looking work.

   Thanks, John for the idea. I hope you do it again.

7. Hi all
   All of the different ways of looking at invoiceing was great. I was chicken to turn my in. I didn’t it would be of the caliber of what was presented. I should have turned it in. I used all valdidation and look up and on the invoice number I wrote a formula that would delete the last used invoice from a drop down list.That way the invoice numbers would always be in order.

8. Charlene:

   got a “can’t find object…” error and went to references to find MISSING: fpdtc 1.0 Type Library.”

   Running Office 2000 Premium.

   Can you enlighten me?

   Thanks
   doco

9. fpdtc = FrontPage Design Time Controls (I think). In any case, that ref is not necessary. In the VB Editor, activate the project and choose Tool * References. Then remove the checkmark from that item.

   Also, Charlene does something that I would not recommend. The ExitButton_Click procedure has this code:

   Private Sub ExitButton_Click()
   Application.DisplayAlerts = False
   Application.Quit
   End Sub

   This shuts down Excel without prompting for unsaved files.

10. My original intention was to utilize a couple after hours at the office and provide a quick and simple response that would be the basis for feedback. After I sent it of course, I thought of a few more things but time is not something I have a lot of.

    The way John handled it is even better! Learned a few tricks from everyone.

    Thanks John! Hope we do more as time goes by. This was great!

    doco

11. I wish I’d found this earlier!

    I’ll have to remodel a template that I’ve provided to folks for a while.

    Thanks.

    Too bad I can’t access the site from work.

    Smitty

12. I tried to make my invoice bullet proof (something hard to do in Excel) for the average user. I started off by making the invoice simple in appearance.

    I also fixed it so if you tri-folded it you could see the address in a windowed envelope (at least the ones I had sitting around the house, yours may vary).

    If you will notice I have validations in place that will determine if the state and zip codes match and warn the user if they don’t with a possible error. I allowed the user to override the error just incase i.e. foreign zips etc..

    Before the user prints the form will loop through the invoice and check for errors like missing values, values with no product entered, missing payment amount etc. and a message box will pop up telling the user of the error then the cursor will move to the location error.

    Next I used “Data Validation” to determine if the credit card number was valid and if the expiration date was expired or not. I also use validation for phone numbers, payment type and tax rate. Again I allow the user to override these error warnings, and if I had more time I would set it up as an option weather the manager could allow overrides or not.

    The tax rate will not be charged for out of state customers by default, but I also added an area where even IN STATE customers would not be taxed should they have a tax ID such as a church, school, or reseller etc..

    I have the invoice set up where every time you start a new invoice it will automatically create a new invoice number.

    I started to lock out the menus, but decided not to because so many times people toggle back and forth in Excel and I was afraid the advantages would not out weigh the disadvantages.

    I didn’t spend a lot of time with the address or phone numbers of the place, just the core app. I figured if it were something I would really use I could always make it prettier.

    As an after thought I put a dropdown list to choose a state if the user didn’t know the state abbreviation. With a little more time I would have had a dropdown list and then the state’s full name displayed as a comment.

    Should a customer buy more items than the allotted lines of the invoice (40 in this case) I added a button where they could continue on adding items until they were compete. What happens when 41+ items are purchased the invoice prints, the total are carried over to a new page, the invoice is cleared, and a new page is started.

    I also created a sheet with some help information.

    I found out about this late so I only worked on it four about four hours, seeing some of the examples gives me some ideas to even improve it.

    I started to use dialog boxes, but decided to keep it simple.

    So what do you think?

    I really liked everyone’s examples, and think we should do this again on a different project.

    Ken

13. P.S.

    Also if the user wants to add an item to the list they must go to the to the Inventory page.

    New items will have blue text, and the user can select to keep the list alphabetical or in the original order by clicking on a sort button.

    Users choose the items from a dropdown list on the Sales Invoice page, and the price is calculated automatically.

    Note: The product list has a name that is DYNAMIC meaning it grows or shrinks according to the amount of products in the list thus making the data validation list to allow the user to always have the correct number of products for selection i.e no blank choices or missing choices.

14. One more comment:

    You can also start typing an item onto the invoice. After awhile 89 product will not be hard to remember and typing them may be faster than selecting one from the list especially if the item you wanted is in the middle of the list. So I expanded the rows added a link to the items in the inventory list, then hid the rows. The results are if a user begins entering Ba – Auto Complete will enter “Bath Ball De-Chlorinator” to the list automatically if the user enters To – Auto Complete will enter “Toyo Filter MW 7000C” no need to type the whole name in or even search from the dropdown list. This will aid the user once they start to learn the product names.

    Also I set the move after direction to go right so pressing ENTER or TAB will move to the next field because movement is restricted to unprotected cells only. When the user closes the file the MoveAfterDirection is returned to the state it was in before the Invoice workbook was opened (nice to clean up after ones self).

    Well I guess I have typed long enough, sorry it’s so long, but I did put a lot of little extras into it.

15. Ken, I’ve been looking at yours and am impressed. Nice use of data validation, dynamic ranges, etc. The credit card # and State v. Zip error checking are something! I also like the way you handle multiple pages.

    I have two suggestions.One is that that your code could be shortened by having a subroutine processing all those identical error checks for blanks – just pass it the range. The other is that you could have data validation for the quantity and prices (that was true for a few of them).

16. Hi doco and John,

    I don’t know what that reference is. Honestly, I don’t know what most of the things in the References window are, or why the ones are marked that are.

    I did realize earlier today as I was reviewing my submission, that it would be better to just close the workbook instead of the entire application. I shouldn’t assume this workbook is the only one open. The specs did say it was not necessary to save anything, but this design flaw affects anything else that might be open in Excel at the time.

    Thank you,

    Charlene

17. Hi There,

    It is interesting to see the different approaches! Very “educative”!
    I’ll try to participate on a next project.

    FYI
    2 Comments:
    General:
    -Actually the first thing I was thinking about was the automatic numbering of the invoice number … this is a legal obligation in Belgium.

    Version:
    -Some projects have difficulties with XL97 … for what I have experienced, the most “serious” issues:
    *Aurelio: Exit =>Error 1004:Close method
    Charlene: Can’t find project or library (on Opening WB)
    *Donald: Automation type not supported in VB when hoovering over buttons/checkbox
    *Doug: Add/delete rows not working? Price not updated => Total not calculated.
    *John: Having selected a product .. it is not off-setting to Quantity and “vice versa”
    *…there are other too…

    It’s just the different approaches that matter though.

    Nice work!!! Sige

18. Sige:

    I forgot to take the code out of the cmdAddCustomer and cmdAddProduct _MouseMove events. I was just goofing around after I discovered there is no ToolTip property for controls in Excel 2K, then got distracted with a phone call…

    It may be this is acting up because StatusBar is out, though no such error comes for me.

    There are a couple things I would do over. One is looping through available CommandBar collection instead of the subjective way I did it – but I was in a bit of a hurry when I did it.

    doco

19. Doco:

    >I forgot to take the code out of the cmdAddCustomer and cmdAddProduct _MouseMove events.
    Removing them resolves the issue …

    Nice work! Sige

20. Ken, 4 hours? Now I’m really impressed!

    Sige, I thought about XL 97, but never got around to checking it. I’ll try to take a look and see where it fails.

21. Doug,

    Thanks for the input. I was thinking about re-doing one just for fun, make it prettier, and add a few touches I thought of later, and use some ideas I got from the examples.

22. One thing I noticed of those that used validation; I checked the “Ignore Blanks” check box on the Data|Validation dialog but blanks are not ignored. Yet y’all got the blanks to be ignored.

    Am I missing something (I usually do :o) )

    doco

23. doco, I’ve never bothered with that setting because I haven’t noticed a difference, but your question made me look a little closer. It seems that if I click into the cell and then press return, the validation will allow it if “ignore blanks” is chosen, otherwise it won’t. However either setting allows a cell that’s “blanked” by clearing contents or hitting the delete key. Also, if you enter a space in the cell, neither setting allows it.

    I’m still not sure what use the setting is, but that’s what I observe.

    Also, j-walk, while I’m here, I want to say that I like your trick of bouncing back and forth between the Item and Quantity. A nice touch.

24. John,
    I hope you do this again. I didn’t send mine in ’cause I wasn’t happy with the formatting yet. I will make sure to submit next time.

25. Doug:

    It just occurred to me reading your post, that “Ignore Blanks” may have another meaning: one not of blanks in the list; but of blanks in the validated cell? hehehe I laugh at myself…

    Thanks for the paradigm shift :o

    doco

26. doco, that’s interesting, I never thought of it that way. It seems like one of those options that nobody would miss if it weren’t there, but maybe (probably) I’m missing something.

    Charlene, I’ve been looking at your code. That was a lot of work! I originally tried using userforms for the same reason you did, to provide the user as much guidance as possible, but I gave up for a similar reason to what you mentioned, I couldn’t show the chosen products all at once in a way that also allowed the user to select new products. I had a lot of frustrating learning experiences with multicolumn combobboxes in listbox mode…

    I found your code well organized and easy to read. I did have a few thoughs…

    1) I think you could remove the validation from the spindown. As it is now, you can’t go back down from a blank item, because it tries to validate that blank item. I commented out from “If ValidItem” through “StoreItem”. Since you don’t allow the user to go up more than one item past the last filled item I think this works. Also, on the spinner, I think you could make your validation shorter by using the Max and Min properties. (I do think that ideally, you’d want the user to be able to return to a previous item by entering the number in the textbox, which means tying it to the spinner, which means more brain damage. I know j-walk has good info on that in his programming book.)

    2) You might consider using a dynamic range for the products list. If you’re not familiar, the range will expand and contract as entries are made. It uses CountA in the range name definition. I used one for my “product_entry” range and I saw that Ken used at least once too.

    3) I think you could use the With, End With statements to shorten up some of the code where your referring to an object more than once in a row. It’s more efficient, plus I think it improves readability.

    I hope you don’t mind that I commented a lot on yours. I just found your code interesting. Also, I’d welcome detailed comments on mine (I hope!). That’s part of what excited me about this idea of j-walk’s, although I’m not really sure what level of comments/criticism is appropriate.

27. Charlene, me again. I was out pulling up tomato plants wondering what I’d told you that was wrong – and one thing came to me. On #1 above, you of course don’t want to disable all validation on SpinDown, you just want to allow blanks. That way if they don’t enter anything they can still go back, but if they do and then go back it will still be validated. I think.

28. Hi, john

    It seems that you have data validation on Qty cells but I can enter any data in those cells. How did you do it?

    Richard

29. Richard,

    In the “Error Alert” tab of the data validation dialog box , uncheck “Show error alert after invalid data is entered.” You then get the data validation list, but no error if the user enters a value not on the list. I had never noticed this before doing this project.

30. The zipped file seems to be incomplete. I get an error when trying to open it.

31. What formula do i use to make a date in a column to turn say red to show its expired. I keep checks on some companies and keep track of some of there insurance items. So i need to look at the chart and see at a glance what is expired and not

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.