Drop down box dependencies

majon96

Board Regular
Joined
Sep 25, 2002
Messages
61
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Larry_S

New Member
Joined
Sep 25, 2002
Messages
10
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

Board Regular
Joined
Sep 25, 2002
Messages
61
Would it be possible to make this work without using VBA, and instead use an if-statement?
 

amandag

Board Regular
Joined
May 20, 2002
Messages
110
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.
 

Forum statistics

Threads
1,144,768
Messages
5,726,179
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top