Is it possible to to use Multiple Combo Boxes to narrow down choices for
specfic data in a worksheet?

Example: User Selects from choices in Combo Box 1, the choice from that
gives the choices for Combo Box 2, which gives you the choices for Combo Box
3?

All the Data to fill Combo Box 3 is in an excel worksheet in the same
workbook as the User Form. I have the worksheet set up as named ranges
"CLLI_GA", etc...

Combo box 1 = Customer (Approx 8 customers)
Combo box 2 = State (Approx 9 States)
Combo box 3 = Office (400 Offices per State)

I got the first combo box to work with the add item method, but I am not
sure how to continue on.

Private Sub UserForm_Initialize()

'Customer Information
With Me.Customer_11
.AddItem ""
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
End With
End Sub

Any help or sugestions would be greatly apprieciated.

Hi Brian,

Called Cascading Combo boxes. Check out the following link.

http://www.excel-vba.com/vba-forms-3-7-combo-boxes.htm

--
Regards,

OssieMac


"Brian" wrote:

Is it possible to to use Multiple Combo Boxes to narrow down choices for
specfic data in a worksheet?

Example: User Selects from choices in Combo Box 1, the choice from that
gives the choices for Combo Box 2, which gives you the choices for Combo Box
3?

All the Data to fill Combo Box 3 is in an excel worksheet in the same
workbook as the User Form. I have the worksheet set up as named ranges
"CLLI_GA", etc...

Combo box 1 = Customer (Approx 8 customers)
Combo box 2 = State (Approx 9 States)
Combo box 3 = Office (400 Offices per State)

I got the first combo box to work with the add item method, but I am not
sure how to continue on.

Private Sub UserForm_Initialize()

'Customer Information
With Me.Customer_11
.AddItem ""
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
End With
End Sub

Any help or sugestions would be greatly apprieciated.

Hi again Brian,

I didn't realize that you have to pay for info at that site. I'll see if I
can put an answer together for you.

--
Regards,

OssieMac

I didn't mean to possibly offend you on my other post about resizing the User
form. I am sorry if came off as kind of rude. I have been working on the
program for a month now and I am getting really frustrated at it. Everytime I
make 3 steps forward i get something like that which really sets me back.

The truth is because of that issue I may not be able to use this program and
thats really disapoiting if you know what I mean.

Again I am sorry if i came acroos as rude.

"OssieMac" wrote:

Hi again Brian,

I didn't realize that you have to pay for info at that site. I'll see if I
can put an answer together for you.

--
Regards,

OssieMac

Don't worry about it Brian. I interpreted it as as simple frustration and I
fully understand that; I often get like it. Matter of fact I am approaching
it now because I am having trouble with the cascading combo boxes; nearly
there and can't get the last bit correct and not sure that I can.

Did you check out my latest option on your other thread for the userform size?

Unfortunately the userform thing is an inaccurate science because when you
adjust the screen resolution the height and width do not remain proportional.
You can adjust the form size quite well but you need to Zoom to adjust the
size of the controls in the form and the zoom affects both height and width
and if not proportional it does not work well.

It would be a lot of work but it should be possibe to identify the screen
resolution and then set the form size and then separately set the size and
position of all the controls in the form.

--
Regards,

OssieMac

After your last post I went on a "Where's Waldo' search for anything on
Cascading Combo Boxes. I found some code that looks like it would work,
except for the fact that i don't under stand it or how it works. Plus there
table is layed out odd. My Worksheet is just a standard table with names,
address, zip, etc... The problem is that if I have to retype them it's going
to take 2 months.

Whats so bad is I only have 2 major hurdles to over come to basically
complete this program.

1: User Form Resize
2: Cascading Combo Boxes

I didn't understand this code, but you probaly will look at it and
understand it. I know that you will have more of a clue than I will.

http://www.xldynamic.com/source/xld.Dropdowns.html

It might come down to paying someone to write the code for resizing the User
Form and thats Ok as long as it gets done. "SOON" LOL

Thanks for all your help


"OssieMac" wrote:

Don't worry about it Brian. I interpreted it as as simple frustration and I
fully understand that; I often get like it. Matter of fact I am approaching
it now because I am having trouble with the cascading combo boxes; nearly
there and can't get the last bit correct and not sure that I can.

Did you check out my latest option on your other thread for the userform size?

Unfortunately the userform thing is an inaccurate science because when you
adjust the screen resolution the height and width do not remain proportional.
You can adjust the form size quite well but you need to Zoom to adjust the
size of the controls in the form and the zoom affects both height and width
and if not proportional it does not work well.

It would be a lot of work but it should be possibe to identify the screen
resolution and then set the form size and then separately set the size and
position of all the controls in the form.

--
Regards,

OssieMac

I like the way the resize works that you posted.


I set it up the test program as a Macro seperate from the program. The user
can run that macro first to get there settings. Can we add a text box for the
user to adjust there settings without having to go into the code? Something
like?

A text box for dblMultWdth = vidWidth / 1446
A text box for dblMultHt = vidHeight / 816

A spin Button for .Zoom = 75 * dblZoom
A spin Button for .Top = 2
A spin Button for .Left = 35

THanks so much for your help on this, I was starting to worry about if it
could be done.

"OssieMac" wrote:

Don't worry about it Brian. I interpreted it as as simple frustration and I
fully understand that; I often get like it. Matter of fact I am approaching
it now because I am having trouble with the cascading combo boxes; nearly
there and can't get the last bit correct and not sure that I can.

Did you check out my latest option on your other thread for the userform size?

Unfortunately the userform thing is an inaccurate science because when you
adjust the screen resolution the height and width do not remain proportional.
You can adjust the form size quite well but you need to Zoom to adjust the
size of the controls in the form and the zoom affects both height and width
and if not proportional it does not work well.

It would be a lot of work but it should be possibe to identify the screen
resolution and then set the form size and then separately set the size and
position of all the controls in the form.

--
Regards,

OssieMac

Hi again Brian,

Userform.
There is no need for each user to run the code to find their own setting.
That code was for you to run on the computer on which you developed the
Userform. Just need a base for the code to start with. When you know what the
parameters are, just edit the code using the returned width and height and
the proportional calculation should adjust the size for the other users.
Unfortunately it is not foolproof coding and you still might find it
unsatisfactory depending on the resolutions being used.

I cant download the workbook from the page you gave me so really not much
chop. Says the page is unavailable.

Anyway I have come up with a solution to cascading ComboBoxes. (Pity it is
not Access because cascading ComboBoxes are basically built in; just need to
know how to manipulate them.)

However, you have now said that you dont want to re-type your raw data so
can you post a sample of your raw data for the combo boxes. Just a few lines
will do. Dont post customer names; just replace them with a bunch of As,
Bs and Cs etc. I want to see if my code will work with your data layout.

--
Regards,

OssieMac

The info is not top secrect, so it's no big deal.

Here are the column headings:

GEORGIA OFFICES
CLLI NAME ADDRESS 1 ADDRESS 2 CITY STATE ZIP GLC Code T-Base

Data in Worksheet
--------------------------------
Clli: ACWOGAMA
Add 1: 4745 Logan Road
City: Acworth
St: GA
Zip: 30101
GLC: F5341

Clli: AGSTGAAU
Add 1: 3523 Washington Street
City: Augusta
St: GA
Zip: 30907
GLC: R3547

Clli: AGSTGABM
Add 1: 1490 Ellis Street
City: Augusta
St: GA
Zip: 30902
GLC: R6341



"OssieMac" wrote:

Hi again Brian,

Userform.
There is no need for each user to run the code to find their own setting.
That code was for you to run on the computer on which you developed the
Userform. Just need a base for the code to start with. When you know what the
parameters are, just edit the code using the returned width and height and
the proportional calculation should adjust the size for the other users.
Unfortunately it is not foolproof coding and you still might find it
unsatisfactory depending on the resolutions being used.

I cant download the workbook from the page you gave me so really not much
chop. Says the page is unavailable.

Anyway I have come up with a solution to cascading ComboBoxes. (Pity it is
not Access because cascading ComboBoxes are basically built in; just need to
know how to manipulate them.)

However, you have now said that you dont want to re-type your raw data so
can you post a sample of your raw data for the combo boxes. Just a few lines
will do. Dont post customer names; just replace them with a bunch of As,
Bs and Cs etc. I want to see if my code will work with your data layout.

--
Regards,

OssieMac

I can have the girl at the office cut and paste it if need be. All it is is
just a list of offices in each state.

The coulmns go across on my worksheet. The example on that web page went
down the page. Do you know how big that worksheet would be to have 400
offices in 9 different states done that way.

What is going to be difficult is when the last choice "CLLI" is choose, I am
going to try and get it to auto fill the rest of the Info into the userform.
That should be fun.

"OssieMac" wrote:

Hi again Brian,

Userform.
There is no need for each user to run the code to find their own setting.
That code was for you to run on the computer on which you developed the
Userform. Just need a base for the code to start with. When you know what the
parameters are, just edit the code using the returned width and height and
the proportional calculation should adjust the size for the other users.
Unfortunately it is not foolproof coding and you still might find it
unsatisfactory depending on the resolutions being used.

I cant download the workbook from the page you gave me so really not much
chop. Says the page is unavailable.

Anyway I have come up with a solution to cascading ComboBoxes. (Pity it is
not Access because cascading ComboBoxes are basically built in; just need to
know how to manipulate them.)

However, you have now said that you dont want to re-type your raw data so
can you post a sample of your raw data for the combo boxes. Just a few lines
will do. Dont post customer names; just replace them with a bunch of As,
Bs and Cs etc. I want to see if my code will work with your data layout.

--
Regards,

OssieMac