Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Data Series Selection from Data Validation Dropdown? | Charts and Charting in Excel | |||
Dynamic Data Validation | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data Validation - Dynamic | Excel Programming |