G
Guest
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
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