Cascading Comboboxes from Control Toolbox

[Dangerous_Dave]

Hi

I've spent ages trawling the net looking for an answer to this but am not having any luck pinpointing exactly what I want.

I want to set up 2 combo boxes - the list displayed in the second being dependant on the item selected in the first. The first combo box refers to a dynamic range, which I set up by typing the appropriate range name into the ListFillRange field in the combo box's properties. Thumbs up so far - great !!!

But now I want the second combo box to pick up the range name selected in the first combo box and to show me the list associated with that. I know it's going to need some code but I can't find any that works. My best hope, I thought, came from using the code in this link:

http://www.ozgrid.com/VBA/dependent-combobox.htm

...but the code didn't work. What I am doing different is to use two unattached combo boxes - ozgrid's example had them embedded in a userform. Does this make a difference at all ??? Property fields for combo boxes on the userform were certainly different from the property fields on my combo boxes. Any example that I found talked about using a userform but never said why. I could take the easy way out and just substitue my data into ozgrid's example. But I feel I'd just be copping out.

I know I don't want to use drop down lists and use the INDIRECT function - this spreadsheet will be used by lots of people and I think this option just looks cheap and nasty.

I know I don't want to use combo boxes from the forms toolbox because of font restrictions and general aesthetics.

I don't think I want to use a userform because it restricts my design more than I'd like.

I'm sure there must be a fairly simple bit of coding required to link the results of combobox1 to combobox2. Could anybody help me with an answer ???

I'm afraid I'm not allowed to post up real information but if anyone could demonstrate with the following simple example:

Range1 = Manufacturer (Ford, VW)
Range2 = Model (Fiesta, Focus, Mondeo; Golf, Passat, Scirocco)

...I'd be more than grateful.

Thanks

DD

 

[MickG]

Hi, If I understand your Requirements this (Modified to suit) should do.

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] ComboBox1.Value
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "Data1": ComboBox2.ListFillRange = "F1:F20"
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "Data2": ComboBox2.ListFillRange = "G1:G20"
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = "Data3": ComboBox2.ListFillRange = "H1:H20"
[COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Norie]

DD

In what way would a userform restrict your design?

Using one might actually help you.

 

[Dangerous_Dave]

MickG - I was actually trying to avoid something like your example. My combobox1 list only has about 20 items in it at the moment. It will eventually have a couple of hundred, so I wanted to avoid having to type out something like "If result is a then..., If result is b then... if result is c then...", because it would take ages and would more than likely cause maintenance problems in the future (I guess). I'm hoping that using dynamic ranges and one piece of code will save a lot of that repetition. But just in case I do end up using this method can I just replace those ranges you've listed with range names eg:

Case Is = "Ford": ComboBox2.ListFillRange = "FordCars"

...or should it be worksheet!FordCars or something similar.


Norie - I was actually thinking on the way home that I probably could still manipulate a userform enough to make this spreadsheet do what I want, so I'll probably try that tomorrow. But I still don't know if embedding the comboboxes in a userform results in a different set of rules to having separate comboboxes floating around a worksheet. Is that my problem ???



After reading my original post back I can sense that I've just come to the end of a very frustrating day, so apologies to everyone if I've come across as a grumpy little toerag...

 

[MickG]

Hi, Just Plain "Named" Ranges is the simpilist. :- "FordCars".
Mick

 

[chuf]

Hi All, fingers crossed that the usual geniuses are lurking on here..

I've a similar problem to this.. and wondered what the code would be to assist.

I have named ranges on a worksheet called LookupLists
Column A has Areas
and Columns H to L has services within those areas.
Column headers H to L are Serv1, Serv2 and so on.

Each area has different services.

I have two combo boxes, what i'd like is depending on which Area is chosen in the first combo box, that the correct column is chosen which lists only those services available in that area. eg So Area A has services 2. Area B shows services 4

My codes at the moment are too garbled to even think of posting here but, that said, i'm amazed i've managed ( with help from this site) to even create combo boxes.. so i'm on a high.

Hope i've explained this ok, as always everything crossed.

Thanks Chuf

 

[Norie]

chuf

Perhaps you should start a new thread.

I think I know what you are trying to say you have but I'm not 100% sure.

You mention named ranges but then you go on to mention 'headers'.