Linking Combo Boxes
 

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?

Linking Combo Boxes
 

One way is to use a range name to access your data for the combo box that
has variable data, and in the other combo assign a macro that changes the
cells defined by the range name according to the selection in this combo
box.


"chwillis" wrote in message
...
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?

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?