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 here:

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