Combo Boxes 1

[pbellc]

Does anyone know how to make combo boxes dependent on one another? In other words, the values in box two would be dependent on what the user selected in box one.

Any thoughts, either using VB or the Excel provided macros/dialogue boxes, would be greatly appreciated. Thanks.

pc

 

[xlbo]

Depends really on where you are using the "combos"
If you are using them in a worksheet, you may be better off using Data>Validation. You can use this in conjunction with the INDIRECT function to create linked dropdown boxes

Other than that, you'll be looking at VBA to get the selection from one box and then populate the controlsource for the next one

Please let me know which route you wish to go down and I'll try and help (but you'll need to be more specific)

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[pbellc]

Geoff-

I am using it in a Worksheet, so the Data --> Validation is probably the easiest solution, right?

The real trick is that the data that is being validated (or selected) will then be used to poluate an SQL query (I don't know if that affects anything or not).

THe first list is about 8 items long, and the second list totals over 100 items, but is about evenly distributed between the 8 items in list 1.

I hope I am being clear. Thanks

pc

 

[xlbo]

Ok - what you need to do is use named ranges
Lets say your 1st range is named "Master"

For those cells where you want to select from this range, select all of them and goto Data>Validation
Select List
enter =Master
in the textbox provided

Lets say the items in Master are:
Apple
Orange
Banana
Lemon
Kiwi
Peach
Pear
Plum

In your secondary list (I assume you just have a big long list of potential secondary selections), you will need to group together all the selections that pertain to the "Master" selection
so, you will need the layout to be
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange
etc etc
Each of these sections should be Named (Insert>Name>Define) as the same as the "Master" selection so you would have one named range as "Apple", one as "Orange" etc etc

Then, select the cells you want to apply the "conditional dropdown" on
Goto Data>Validation and choose List
Enter =Indirect(A1)
where A1 is the 1st cell that has the "Master" dropdown in

What this will do is pick up the selection from the master combo and use the INDIRECT function to return a reference to the list based on that selection

HTH

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[pbellc]

Thanks, Geoff. That worked perfectly!

 

[ajking]

Following with this the combo box question, how would you do it using VB? TIA

 

[xlbo]

If you mean you want to link actual combo boxes on a form, you will need a list of master/slave like
CarColour Car
red Ford
red Citroen
red BMW
silver BMW
silver Merc
silver Audi
yellow Fiat
yellow Nissan
yellow Subaru
etc etc etc
to populate the 1st combo, use the form initialise event with something like

cbColours.clear
For each c in range("A2:A100&quot
if c.offset(-1,0).text <> C.text then cbColours.additem (c.text)
next

then in cbColours change event use

for each c in range(&quot;B2:B100&quot
if c.offset(0,-1) = cbcolours.text then cbCars.additem(c.text)
next



Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[ajking]

seek and ye shall find. Ask and ye shall be answered!!
many thanks Geoff