Dynamic Drop Down Boxes
 

Hi,

I'm a first time caller, long time listener who is wondering if anyone has
run into a similar situation. I'm using a excel 2000 spreadsheet to track
users activities. There are two drop down form controls on the worksheet, the
first is a static group of choices. I defined a group name and entered it in
the Format Control box Input Range field for the choices. The second Drop
Down box can choose from a total of 10 different group names of subcategories
depending on what the user selects in the first.

I can accomplish this if I use combo boxes by placing the following code in
the OnChange event of the first field, but I use 3000 total fields, long
story.....and combo boxes take up too much space for them to be effective.

I've tried VLookup and HLookup and even If statements but the closest I can
get is using the IF statement, and then I only get the first choice in the
subcategory group to display. I'd greatly appreciatte any help you could lend
me.

Thanx, Scooter

Function Choice(temp)

Select Case temp
Case "A": temp = "A"
Case "B": temp = "B"
yada..yada...yada
End Select

End Function

then I use Combox1.ListFillRange = temp 'to set the choices.

Instead of combo boxes, you can create dependent data validation lists.
There are instructions he

http://www.contextures.com/xlDataVal02.html


Scooter wrote:
Hi,

I'm a first time caller, long time listener who is wondering if anyone has
run into a similar situation. I'm using a excel 2000 spreadsheet to track
users activities. There are two drop down form controls on the worksheet, the
first is a static group of choices. I defined a group name and entered it in
the Format Control box Input Range field for the choices. The second Drop
Down box can choose from a total of 10 different group names of subcategories
depending on what the user selects in the first.

I can accomplish this if I use combo boxes by placing the following code in
the OnChange event of the first field, but I use 3000 total fields, long
story.....and combo boxes take up too much space for them to be effective.

I've tried VLookup and HLookup and even If statements but the closest I can
get is using the IF statement, and then I only get the first choice in the
subcategory group to display. I'd greatly appreciatte any help you could lend
me.

Thanx, Scooter

Function Choice(temp)

Select Case temp
Case "A": temp = "A"
Case "B": temp = "B"
yada..yada...yada
End Select

End Function

then I use Combox1.ListFillRange = temp 'to set the choices.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html