pull down menu

willyrast

New Member
Joined
Oct 6, 2006
Messages
13
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Joined
Jul 30, 2006
Messages
3,656
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
ABCDEFGH
1AustraliaAustralia-City4CountryIndiaUSAustralia
2
3AustraliaIndia-City1US-City1Australia-City1
4IndiaIndia-City2US-City2Australia-City2
5USIndia-City3US-City3Australia-City3
6India-City4US-City4Australia-City4
7India-City5US-City5Australia-City5
8US-City6Australia-City6
9US-City7Australia-City7
10US-City8
11US-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
 

willyrast

New Member
Joined
Oct 6, 2006
Messages
13
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

New Member
Joined
Oct 6, 2006
Messages
13
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.
 

Forum statistics

Threads
1,136,708
Messages
5,677,317
Members
419,687
Latest member
ash59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top