Linking Combo Boxes
Hi,
Something along these lines should help:
Combobox1 is initialized with list of car manufacturers based on named range
"Manufacturers".
When combobox1 is selected, combobox2 is loaded with models from named
range; if manufacturer is "Ford" then named range is "Ford_Models".
Similarly, combobox3 is loaded with colours for a given model e.g. named
range "Fiesta_Colours" (UK car models here!)
You could use arrays of named ranges in combination with the ListIndex of a
combobox (or both) to get the date to be loaded.I personally think named
ranges are the best way of organising the data.
HTH
Private Sub Combobox1_Change()
Dim rng As Range, cell As Range
' Set combobox2 with list of models for given manufacturer
Set rng = Range(ComboBox1.Value & "_Models")
ComboBox2.Clear
For Each cell In rng
ComboBox2.AddItem cell.Value
Next cell
End Sub
Private Sub Combobox2_Change()
Dim rng As Range, cell As Range
' Set combobox3 with list of colours for given model
Set rng = Range(ComboBox2.Value & "_Colours")
ComboBox3.Clear
For Each cell In rng
ComboBox3.AddItem cell.Value
Next cell
End Sub
Private Sub Userform_Initialize()
Dim rng As Range, cell As Range
Set rng = Range("Manufacturers")
For Each cell In rng
ComboBox1.AddItem cell.Value
Next cell
End Sub
HTH
"chwillis" wrote:
I want to have a combo box data change based on what another combo boxes
selected value is. Is this possible in Excel 2000, and if so how?
|