Data Validation Lists dependent on other Lists
Data Validation Lists dependent on other Lists
With a solution:
rajibdas
Aug 31, 2006 9:19 am Post subject:
I have adjusted the solution from 'rajibdas':
In a test spreadsheet create the following:
Cell A1 contains a Data Validation List looking at column 'E', cells 'E2:E6':
Data Validation Lists dependent on other Lists.xls |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | Australia | Australia-City4 | | | Country | India | US | Australia |
---|
2 | | | | | | | | |
---|
3 | | | | | Australia | India-City1 | US-City1 | Australia-City1 |
---|
4 | | | | | India | India-City2 | US-City2 | Australia-City2 |
---|
5 | | | | | US | India-City3 | US-City3 | Australia-City3 |
---|
6 | | | | | | India-City4 | US-City4 | Australia-City4 |
---|
7 | | | | | | India-City5 | US-City5 | Australia-City5 |
---|
8 | | | | | | | US-City6 | Australia-City6 |
---|
9 | | | | | | | US-City7 | Australia-City7 |
---|
10 | | | | | | | US-City8 | |
---|
11 | | | | | | | US-City9 | |
---|
|
---|
Copy the following code:
'-------------------Start of Code---------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'
' The following code was modigied on 09/13/2006 by Stanley D. Grom, Jr.
' The original code was posted to Mr. Excel
' by rajibdas, Aug 31, 2006 9:19 am Post subject:
If Intersect(Target, Range("A1")) Is Nothing Then 'cell where you'll have the inital dropdown list
Exit Sub
ElseIf Range("A1").Value = "" Then ' if cell A1 blank, blank B1
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation ' create the second dropdown on cells B1
.Delete
End With
Range("A1").Select
Exit Sub
ElseIf Range("A1").Value = "India" Then ' if cell A1 is the name you select
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation ' create the second dropdown on cells B1
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$F$2:$F$8" ' the drop down for states (change to where ever the entire list is)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub
ElseIf Range("A1").Value = "US" Then
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$2:$G$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub
ElseIf Range("A1").Value = "Australia" Then
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$H$2:$H$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub
End If
End Sub
'-------------------End of Code---------------------
Press the 'ALT' and 'F11' keys, and paste in the 'Microsoft Excel Objects' Sheet1(Sheet1)'
I hope this helps.
Have a great day,
Stan