Combo Boxes

[xlhappy]

I have two Combo boxes. Based on the selection in Combo1, the list of available choices in Combo2 changes. Once a selection is made in Combo2, should the user change their mind and go back to Combo1 to make a different selection, the original Combo2 selection is still visible in the Combo2 box. I'd like to clear it when the user makes a new selection in Combo1. I've tired to do a cbo1. Value = "" and cbo.1.Text = " ", but neither seems to work. Any idea how to clear a previous value in a Combo box ?

 

[Greg Truby]

Combobox2.ListIndex = -1

 

[xlhappy]

Hmmmm . . . nope, that didn't work. Plus, I don't see ListIndex in the ComboBox properties list?

 

[Greg Truby]

Are these activeX controls? Or forms controls?

 

[xlhappy]

ActiveX.

 

[Greg Truby]

You'll see a ListIndex property if they are on a userform. You won't if they're on a worksheet. But even if the comboboxes are on a worksheet, you can still hit the ListIndex property programatically and set it to -1.

 

[xlhappy]

This is the code I put in per your suggestion:

ActiveSheet.Shapes("ComboBox2").Select
Selection.ListIndex = -1

and it errors out.

 

[Greg Truby]

I wouldn't use the Shapes collection to access the control. If you put the code straight in the worksheet's code module as an event handler for Combobox1's change event then when typing code into the ws's code module, if you use the keyword ME then Intellisense will list any activeX controls for you when you hit the period after ME ; the following code works just fine for me.

Private Sub ComboBox1_Change()
    Me.ComboBox2.ListIndex = -1
End Sub

 

[xlhappy]

Works like a charm ! ! ! ! ! Thanks a gizzillion.