Drop down box dependencies

[majon96]

Is there a possibility to have one drop down box to depend on another, so that what appears in the dropdown box depends on the choice I made in a previous dropdown box?

Thx
/Magnus

 

[Larry_S]

In the VBA help files, I think the control you are referring to is called a "combobox", which is a text box that shows the currently selected value combined with a dropdown list of possible values.

I suggest that in the Change event code for the first combo box, you evaluate its new value, compute what corresponding value you wish to have appear in the second combobox, and set the ListIndex property of the second combobox so that the desired item from its list appears in its text box.

You need to be careful of ALSO doing the same kind of thing to try to set the first box when the user changes the second box, since that could lead to an infinite loop. One box can control another's default value simply, but two interacting with each other is more complex.

Also, the above assumes that each list has only a single column of data. If there is more than one data column, then things change quite a bit in practice bacause of the multiple columns, altho the basic idea is still the same.

 

[majon96]

Would it be possible to make this work without using VBA, and instead use an if-statement?

 

[amandag]

I posted a very similar problem recently and was given a very simple, very good answer, don't know how to attach directly from the board, so I've pasted it in, hope it's readable.

Aladin Akyurek
.

Joined: Feb 15, 2002
Posts: 4041
From: The Hague, Netherlands Posted: 2002-02-17 09:50

Quote:


On 2002-02-17 07:20, Gurps wrote:
if I use the data validation method, how do I lay out the table of data??

I would prefer to use "real" combo boxes.Would you be able to tell me how to do this method as well?



I don't believe the ComboBox would suit your purpose. The "Input range" appears not to like INDIRECT formulas.

As Juan suggested, it's more convenient to use Lists thru data validation.

The method is as follows:

Enter in some column what follows:

{"USA";"FRANCE"}[ That's, enter USA in a cell, then FRANCE in the next cell down ]

Select these cells, go to the Name Box on the Formula Bar, and type COUNTRIES followed by enter.

Enter in a column next to COUNTRIES:

{"New York";"Pittsburgh";"Los Angeles";"Boston"}

Name this range of cells USA via the Name Box as described above.

Enter in a column next to USA:

{"Paris";"Nice";"Toulon"}

Name this range FRANCE.

Just to see how this works,

activate A1 in some worksheet in the same workbook;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=COUNTRIES

click OK;

activate another cell in the same worksheet, say, C1;

activate Data|Validation;

choose 'List' for 'Allow';

enter as 'Source' the formula:

=INDIRECT(A1)

click OK.

Now you have two lists of which the 2nd depends on the selection from the 1st.