Combo box dependant on another combo box
Posted by Joe Librizzi on September 07, 2001 12:55 PM
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.
Posted by Ivan F Moala on September 07, 2001 6:29 PM
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
