Excel Linking drop down boxes

I would like to insert two drop down boxes in a file. For instance one that contains the states and another that contains the cities. However, I want one to be dependant on the other, i.e. when California is picked as the state, I only want cities in California to be displayed in the other box.

Does anyone know how to do this. My list contains all the information.

in the source box where you put the range for the second drop down selection, you can use an if statement to set up multiple lists based on the first drop down box

Name a Column range that contains only the States. Then build a column for each State and name the column of Cities in a State by the State name.

Then build a list with the source being the name of the State list. When a user selects a State from this list it becomes the source for the City cell dropdown. This code will build such a list. The State list is in B1, the City list will be in D1, if you have created the named ranges for the collection of States and individual lists of Cities contained in a State, each named the State.

Sub BuildList()
'By Joe Was
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End Sub

This code is designed to run each time it is needed by the user and is placed in a sheet module. It could be converted to an envent macro so each time the State cell value changes it will run automatically?

I did not have time just now to play with that version of the code though! JSW

This code works as above and it also gos into the sheet module, all the conditions are the same as for the other code. This will automatically change the list based upon the state entered in B1, updating the list in D1. Given the named ranges are correct. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Linking drop down boxes

Re: Linking drop down boxes

Re: Linking drop down boxes

Re: Linking drop down boxes Auto UpDate Code!

Check out our Excel VBA Resources