I want to create dropdown lists in excel, it should work as follow: In cell2,1 must be a drop down list with 4 names (e.g. Mill, Comp, Loads, Blending), if I select say Mill then in cell2, 2 a dropdown list with all the mill parts must appear, same for the other if I select one of the other names their parts list must appear. Can anyone show me how to do this?
- On a new sheet (let say sheet2) in Cells A1, B1, C1 and D1 enter values Mill, Comp, Loads, Blending respectively
- Put the values of Mill, Comp, Loads and Blending under its respective column heading (make sure no blank line)
- Go to Formula Tab on menu
- Select Cells A1:D1
- Click on define Name
- Enter the name, lets say "MyMainList" and click Ok
- Select all the values in col A, starting from A2 and click on name, give it name "Mill"
- Select all the values in col B, starting from B2 and click on name, give it name "Comp"
- Select all the values in col C, starting from C2 and click on name, give it name "Loads"
- Select all the values in col D, starting from D2 and click on name, give it name "Blending "
- Go to main sheet (lets say sheet1) and select cell A2
- Go to Data tab on menu and click on Data Validation
- Click on the drop down titled "Allow" and choose List
- In Source box write =MyMainList
- Optionally go to input tab and error tab to enter messages for that
- Press ALT+F11
- Double click on "sheet1" in the project explorer
- Paste code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Dim mainsrc As String
Dim subsrc As String
Dim lThisRow As Long
lThisRow = Target.Row
If Cells(lThisRow, 1) = "" Then
mainsrc = Cells(lThisRow, 1).Value
On Error Resume Next
subsrc = Cells(lThisRow, 2).Validation.Formula1
On Error GoTo 0
If ("=" & subsrc = mainsrc) Then
Cells(lThisRow, 2) = ""
With Cells(lThisRow, 2).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & mainsrc
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "This is my Input Title"
.ErrorTitle = "Oops Error"
.InputMessage = "Select a Value"
.ErrorMessage = "Not a valid value"
.ShowInput = True
.ShowError = True
Application.EnableEvents = True
Thanks to rizvisa1
for this tip on the forum.
Published by aakai1056
Latest update on March 5, 2012 at 05:36 AM by aakai1056.