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?

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?

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?