use selected value from one combobox to populate another combobox

[rjudge]

I need to set up 2 ComboBoxes. ComboBox1 contains two choices; "backen
tools" or "frontend tools". Depending on the selected choice ComboBox
will bring up a list of tools relating to that choice. So what I nee
to know is how to pass the selected ComboBox1.Text value to ComboBox2
Once I have the value I can put an 'if' statement in the ComboBox2 cod
such as:

if passed value = "backend" then
ComboBox2.AddItem("tool1")
ComboBox2.AddItem("tool2")
end if

if passed value = "frontend" then
ComboBox2.AddItem("tool10")
ComboBox2.AddItem("tool11")
end if
Cheers

Robbie:confused

 

[GS]

Assuming your code is behind the userform that contains both ComboBox
controls, you could try this in the Change event for ComboBox1

If Me.Value = "backend" Then
With ComboBox2
.AddItem "tool1"
.AddItem "tool2"
End With
Else
With ComboBox2
.AddItem "tool10"
.AddItem "tool11"
End With
End If

HTH
Regards,
Garry

 

[Tom Ogilvy]

Think Gary left out part of his code: (ME refers to the Userform containing
the code or if on a worksheet, the worksheet containing the code module).

If Me.Combobox1.Value = "backend" Then
With Me.ComboBox2
.AddItem "tool1"
.AddItem "tool2"
End With
Else
With Me.ComboBox2
.AddItem "tool10"
.AddItem "tool11"
End With
End If

 

[GS]

Thanks Tom, ...you're absolutely correct.

To the OP:
My apologies for the careless omission!

Also, you may want to include clearing the ComboBox2 list before adding any
items in case the user changes selections in ComboBox1. Otherwise, the new
items will be added to any existing items in the list. Also, I added a line
to clear the .Value so nothing is selected.

Revised (with Tom's correction) to include:

If Me.Combobox1.Value = "backend" Then
With Me.ComboBox2
.Clear
.AddItem "tool1"
.AddItem "tool2"
End With
Else
With Me.ComboBox2
.Clear
.AddItem "tool10"
.AddItem "tool11"
End With
End If
Me.ComboBox2.Value = "" 'make sure nothing is selected

Regards,
Garry