Customize Excel Form? Or create UserForm?

[edinger]

Hi mrexcel forum.

Thanks for a great forum. Until recently I was an excel n00b, but my needs for an excel application drove me to acquire knowledge through this forum and other sources online, and I've now accomplished most of my task:

I've created an .xlsb in excel 2011 for mac that contains four worksheets:

1. product list
2. customer list
3. invoice
4. zipcodes/countries (for lookup - limited to zipcodes in Denmark, Faroe Islands and Greenland)

Based on mostly VLOOKUP and IF functions I've managed to draw info from the customer list and productlist into the invoice. And in the customer list, entering a zipcode will draw the corresponding cityname and country into the appropriate cells.
With VBA I've managed to prevent saving, only save as works. And I've created a custom function TIMESTAMP(), that I call to timestamp my customer list entries with a "date added".

BUT MORE WANTS MORE!!!

As I'm not the only one using this document I'd like to make it more easy to use and more fool proof.
For now I'm using the built in Form in excel (Data -> Form).
It does a nice job, but I'm missing a couple of custom features:

1. I would like to place a button in the "invoice" worksheet, that will bring up the form so I can enter new customers or edit customers right there.
2. I would like one of my entry fields in the form to be multiple line. In the customer list there's a column called "comments".
   This stores manually entered info on the customers, and will be updated every time I deal with the same customer or handle support issues.
3. It would be easier to read and edit this info if it's on a multiple line field with text wrap instead of a single line.
   I like the "criteria" button in the excel form. It enables me bring up any customer by searching the range for either a name, emailaddress, phone no, customer no and so on.
   When I search for a customer and edit the info, my only option to save the info to the entry is to close the form. Would it be possible to add a "save" button?

So the big question remains:

• Can I customize the excel form to meet me needs?

• Or will I have to endeavour into building a UserForm with VBA code?

I think I almost know the answer, but I'm hoping for some advice and pointing in the right direction. Maybe there already exist some code/add-in that will solve my issues?

I've been looking at this tutorial:
http://msdn.microsoft.com/en-us/library/aa192538(v=office.11).aspx

It does cover some of my issues, but lacks the ability to be called from another worksheet as well as the whole "criteria" feature of the excel form. Instead it has a RowNumber function to navigate through the customer list entries. However my customer list will hold more than 1500 entries so this is not an efficient method of searching.

Would it be possible to built something like the "criteria" button and the "button to call from another worksheet" into the above tutorial or would I have to start out completely differently?

I hope someone in here will take time, having already taken the time to read my long story, to offer some advise and point me in the right direction.

So far, my Excel/VBA knowledge extends to pasting code and editing it for mac (swapping "," with ";" and so on), but I've managed to experiment my way to make some working IF with nested IF's with nested IF's... That's fun!

Thank you anyone who read this far!

Best, Thomas from Denmark.

 

[edinger]

Hi again.

Here is my document with some example data:

http://dl.dropbox.com/u/3439857/example.xlsb

Yes, it contains macros. One to disable "save" only enabling "save as" and one to store a custom function called TIMESTAMP().

Here's a breakdown of how the data is used - only for your reference:

In worksheet "kundeliste":

• Columns G and H looks up the city name and country from worksheet "postnumre" based on the zipcode number in column F.
• Contents of Column A is the customer number which is the customers phone number. Therefore column I "Telefon" is just replicating contents of Column A and formats as a phone number.
• Column L utilizes the aforementioned custom function TIMESTAMP() to provide a "date added" value into the cell when a new customer number is entered.

In worksheet "faktura" - invoice:

• D6 is where the customer number is entered/chosen from the list.
• In worksheet "kundeliste" it looks up the organisation name and puts it in B8 and the EAN/CVR-nr in D9. B9 displays "EAN-nr.:" if D9 value is higher than 100000000 (EAN number is 13 digits) and "CVR-nr.:" if D9 value is btw 1000000 and 10000000 (CVR-nr is 8 digits). If theres no value in D9 the CVR or EAN label won't display.
• It also looks up navn (name), adresse (address), postnr (zip), by (city), land (country), telefon (phone number) and email.
• If the country in B13=Færøerne (Faroe Islands) OR B13=Grønland (Greenland) the cells L41 and Q41 change as Faroe Islands and Greenland have different moms (VAT) rules. The invoice total will be based on the product amount excluding "moms" (VAT).
• B19 stores "varenummer" (productnumber). It calls "varebeskrivelse" (product description), price and enters the value "seriel nummer" (serial number) in E20 for the user to manually enter the serial number of the item being sold.
• It also calls a potential discount to O19 - "Rabat". O18 inserts the label "Rabat" (discount) if there's discount info stored. O19 first looks for a discount defined in the customerlist (resellers get 30% discount). If no discount in the customerlist, a potential discount from the product list. Some products may be on sale. If no discounts at all, then left blank and no "Rabat" label.
• P19 is the number of products. It will simply display the label "antal" to remind the user of the invoice to enter a number in that cell in order to calculate the price in Q19.
• All products are then calculated into "Varer ialt" (products total), "varer ex moms" (products excl VAT) and "moms udgør" (VAT value).
• Shipping is added = "forsendelsesomkostninger" - again the value "HUSK PORTO" (Remember shipping) is there to prevent the Invoice total from being calculated without the user entering a shipping cost.

Thanks again to everyone taking the time to read through my work.

Please read my first post in this thread about the Excel Form vs UserForm and share any input that you might have.

Thomas

 

[xenou]

Personally, I'd just buy some software for handling orders and invoicing. But if you really want to build your own bespoke system, why not do it "right" and use a database? Excel won't scale up very well (to the thousands and tens of thousands of transactions, customers, suppliers, invoices, items etc).

ξ

 

[edinger]

Hi Xenou. Thanks for chiming in!

You've got a real point. The reason I started out doing this in Excel was that I saw Excel invoice systems being sold, and I thought, "hey maybe I can make this myself".

I'm on a mac. Can you recommend any database systems? I know of Filemaker and Bento. Will they co-operate well with Excel?

So, would I keep my data lists in Filemaker and still make the invoice in Excel - calling info from Filemaker? Or can you suggest something else?

One other important criteria of the system is that the file must be kept in the cloud. As of now, we're keeping the Excel file in a dropbox folder, which makes it accessible to all three users - however not simultaniously.
The invoice systems that I found, with cloud capabilities, were pretty expensive. We'd rather not have pay a monthly fee just to do invoices and keep a customer database.

I should also mention that it's a small business dealing with niche products.. The product list is probably not going to grow much - 200 items, tops! The customer list is expected to be around 2500-3000 - slightly growing, but not exploding...
Thanks again for you input!

 

[xenou]

Interesting. I'm not an expert in small business systems but I would think something like Quickbooks or Peachtree has to be available for basic inventory and billing - probably in the cloud too now. I've never actually used or seen any invoicing systems in Excel (well, except my own, which is a worksheet that says date, description, qty, amount and adds it up at the bottom -- if anyone would like to buy it I'll entertain offers ) . I've seen some very slick stuff in Excel, and don't doubt it can be the basis for some very sophisticated applications.

If you were to employ a database for data storage and retrieval, any one you like will probably do the job. Just adding a database doesn't make it easier (actual, for design and development its probably harder - but hopefully more robust when its completed). It really depends on the task at hand. If you think the job can be handled in Excel go for it. Obviously, inventory and sales gets complicated quickly as business grows - but your requirements sound more modest at this time. Though I don't really know what it would mean to keep a Excel file "in the cloud".

 

[edinger]

Hi Xenou.

Thanks for your comment and appologies for my late response.

I've been looking at Filemaker and it seems there's template that will do exactly what I need....

Thanks for your input.

best,

Thomas