Dynamic Data Validation
 

Hello

What I want to be able to do is to have two dropdown boxes on a worksheet so
that the selection in the first (dropdown1) governs what is shown in the
second (dropdown2).

In cell A1 I have used data validation to display the range A2:A4. This
displays "Head Office", "Branch1", & "Branch2". These are my offices. When I
select "Head Office" I want dropdown2 (in cell B1) to display range B2:B4
which has three names of employees employed at Head Office. Similarly, if
"Branch1" is selected then dropdown2 will disply C2:C4 which contains the
names of my Branch1 employees. Simple enough?

I have tried Data Validation and then selecting 'Custom' and typing in a
formula along the lines of...

IF(A1="Head Office",B2:B4,"")

but this does not work.

I tried using VBA events handlers. I tried using Worksheet_Change(ByVal as
Excel.Range) but when I change the selection in dropdown1 there is no effect.
It seems like VBA is not recognising the event. (By the way, I have used
EventEnabler = False etc).

It seems to me that my problem must have been encountered before. Any ideas?

I would like to avoid using VBA list boxes of comboboxes as they look messy
on the sheet and cannot be sized to a single excel cell.

I hope there is help at hand...

Alex

Dynamic Data Validation
 

bookmark Debra's site
http://www.contextures.com/tiptech.html

look for datavalidation - dependent lists



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Alex wrote :

Hello

What I want to be able to do is to have two dropdown boxes on a
worksheet so that the selection in the first (dropdown1) governs what
is shown in the second (dropdown2).

In cell A1 I have used data validation to display the range A2:A4.
This displays "Head Office", "Branch1", & "Branch2". These are my
offices. When I select "Head Office" I want dropdown2 (in cell B1) to
display range B2:B4 which has three names of employees employed at
Head Office. Similarly, if "Branch1" is selected then dropdown2 will
disply C2:C4 which contains the names of my Branch1 employees. Simple
enough?

I have tried Data Validation and then selecting 'Custom' and typing
in a formula along the lines of...

IF(A1="Head Office",B2:B4,"")

but this does not work.

I tried using VBA events handlers. I tried using
Worksheet_Change(ByVal as Excel.Range) but when I change the
selection in dropdown1 there is no effect. It seems like VBA is not
recognising the event. (By the way, I have used EventEnabler = False
etc).

It seems to me that my problem must have been encountered before. Any
ideas?

I would like to avoid using VBA list boxes of comboboxes as they look
messy on the sheet and cannot be sized to a single excel cell.

I hope there is help at hand...

Alex

Dynamic Data Validation
 

See http://www.xldynamic.com/source/xld.Dropdowns.html

--
HTH

Bob Phillips

"Alex" wrote in message
...
Hello

What I want to be able to do is to have two dropdown boxes on a worksheet
so
that the selection in the first (dropdown1) governs what is shown in the
second (dropdown2).

In cell A1 I have used data validation to display the range A2:A4. This
displays "Head Office", "Branch1", & "Branch2". These are my offices. When
I
select "Head Office" I want dropdown2 (in cell B1) to display range B2:B4
which has three names of employees employed at Head Office. Similarly, if
"Branch1" is selected then dropdown2 will disply C2:C4 which contains the
names of my Branch1 employees. Simple enough?

I have tried Data Validation and then selecting 'Custom' and typing in a
formula along the lines of...

IF(A1="Head Office",B2:B4,"")

but this does not work.

I tried using VBA events handlers. I tried using Worksheet_Change(ByVal as
Excel.Range) but when I change the selection in dropdown1 there is no
effect.
It seems like VBA is not recognising the event. (By the way, I have used
EventEnabler = False etc).

It seems to me that my problem must have been encountered before. Any
ideas?

I would like to avoid using VBA list boxes of comboboxes as they look
messy
on the sheet and cannot be sized to a single excel cell.

I hope there is help at hand...

Alex