Excel Combo box dependant on another combo box

Hello. I'm trying to make the choices appearing in one combo box dependant on what is choosen in a previous combo box (i.e. if the user chooses "California" in the first box, "Sacramento", "LA" and "San Diego" appear in the second. If he chooses "Texas", "Dallas", "Houston" and "Austin" appear). I've written a macro that assigns a range name to the appropriate list for each original choice. When I put the range name in the second combo box's input range, it tells me that it's "Reference is not valid".

If anyone has any ideas to get around this, I'd REALLY appreciate the help.

Hi Joe
Assuming you have a named range for [states]
and each state has a Named range then the following should work.....

Option Explicit

'This combobox selects the state
'combobox2 contains the names that appear
'when you select the state eg LA,San Diego etc

Private Sub ComboBox1_Change()
On Error GoTo Ex
ComboBox2.Text = Application.Range(ComboBox1.Text).Item(1)
ComboBox2.RowSource = Application.Range(ComboBox1.Text).Address

Exit Sub
Ex:
With ComboBox2
.RowSource = ""
.Text = ""
End With
End Sub

Private Sub UserForm_Initialize()
ComboBox1.RowSource = Application.Range("states").Address
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

Combo box dependant on another combo box

Re: Combo box dependant on another combo box