pull down menu

[willyrast]

Hi everyone
I'm trying to make 2 pull down menus
The first on will display A, B and C
The second one will display something depending of the first one
If A or B is selected in the first one then the seconde one will display E, F and G but if C is selected in the first one than the second one will display only F and G

Hope that someone can help me with this problem

 

[stanleydgromjr]

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
Sheet1

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

 

[HalfAce]

Here are a couple of links to how to do that without vba.
(Nice code Stan. Looks like it should work quite well.)

Joe Was:
http://www.mrexcel.com/board2/viewtopic.php?p=482686

Aladin:
http://www.mrexcel.com/board2/viewtopic.php?t=53213

 

[willyrast]

Thanks man
But the next thing is that i have 50 pull down menu's ( A1:A51)
How do I make it so it will work for all the 50 the same way you showed now?

 

[willyrast]

I'm not a programer but with the help of Stan i solved one of the problems. The next problem is that instead of pull down memu's in cell A1 and B1 i've 50. In cell A1:A51 en so on. Can any one help me with this? I am now using the code Stan posted.

 

[tusharm]

The only solution that makes any sense to me is one that can work with data in a relational database. One approach that does just that is at
Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html
While other approaches have intuitive appeal, they are not scalable and require *structural* changes whenever data values are added/deleted!

I'm not a programer but with the help of Stan i solved one of the problems. The next problem is that instead of pull down memu's in cell A1 and B1 i've 50. In cell A1:A51 en so on. Can any one help me with this? I am now using the code Stan posted.