Userforms

**Joemama** · 08-17-2008, 05:04 PM

I have a pricing sheet from my company that we use to bill with.. there are quite a few choices people use the filter to get the right pricing mix, and then they calculate it out on a calculator.. I know there is a better way.. I want to be ambitious and use a form that references the tab with all of the pricing, and calculate the pricing, but I dont know where to start...

**Tom Schreiner** · 08-17-2008, 05:33 PM

Hi Joe.

Start by providing details. Before I start listing suggestions, I think you may be better off attaching an example. As of your initial post, I have nothing to go on...

**Joemama** · 08-17-2008, 06:00 PM

Hi, thanks for replying. Attached is a very small example, I have taken out some columns and many rows. the user must determine the lane by making his choices of country/city of origin, and country and city of destination. Then there are several fees with which they may have mins, may not and depending on the weight, will get the calculated rate. I hope to be able to have a user form which can allow the user to make the selections(drop down boxes?) and input the weight, and have the calculations appear as line item fees, which they can print, and then restart. I hope I was clear. if there are examples of how others have done this, i would appreciate any help..
thanks again..

**Tom Schreiner** · 08-20-2008, 09:40 AM

Sorry for the delay. Iv'e had trouble getting to this site for the past several days.

Anyway, you should first consider creating some dependant lists.

For example, a userform might contain a combobox named Origin_Country. Depending on the user's selection, you would trim down your other lists based upon the country selected. Only list the applicable Origin Cities for example. Then based upon the city selected, trim down the available airports. Ect...

A database application is a better suited container for your data. However, if that is not an available option, Excel can be used to create an ISAM structured database that will behave relationally.

In any case, you will need to create a list for each field.

**Joemama** · 08-20-2008, 10:45 AM

Hi Tom
Sorry to ask, but have limited experience with VBA and Userforms, but am willing to dig in. This sheet is so very common in my industry, and i must find a way to shave time from filtering through it each time. Is there any examples out there that show dependent lists as you mention? I will look through them and see if cant create something for my project.
Thanks again for your help...

**Tom Schreiner** · 08-20-2008, 01:06 PM

No problem. It going to cost you some time to save some. If you are up to the legwork involved, start with the following.

A1:A? of a new worksheet should contain a list of all of your countries. Assumming that Origin and Destination countries are the same, no further information is neccesary in this field. Name the entire column Countries.

In column B an exhaustive list of every city must be listed. That is, every city located within the countries listed in column A. Once again, I'm assumming that any origin city may also serve as a destination city. In column C, you will need to indicate which country each city belongs to. I would use the country name as the index.

In column D, repeat the process for airports noting in column E which city the ariport belongs to.

This is longhanded but results in the greatest flexibility down the road.

For example, lets say we have a country that has one qualifying city that contains one qualifying airport. It would only be neccesary for the user to select the country. The other fields would be populated automatically.

Of course, this is not usually going to be the case, but the principle applied across the board will result in much easier queries. For example, I don't know, but perhaps certain airports have certain fees. Using this method, the selection of fees could be limited to the airport selected.

In any case, for now, return an example with the countries, cities, and airports. I'll give you an example that you should then be able to extend to your other fields...