Combo box dropdowns

[jondallimore]

Hello,

I am using an activeX combo box to produce a dropdown menu from a named range.

Is it possible to create a diminishing list, ie once a choice is chosen, it disappears from the list (but not from the named range)?

It would need to come back onto the list if it is de-selected at any point.

If anyone can point me towards the right brick wall to bash my head against til its squishy, I would be greatful.

Thanks
Jon

 

[Norie]

If you are directly populating the combobox from the named range then you can't remove items from the list without removing them from the range.

 

[jondallimore]

ok, will just have to live with it then.

 

[Norie]

What you could is use code to populate the combobox from the range and then you would be able to remove items from the list, without them being removed from the range.

You could do that in the activate event of the worksheet the combobox is on.

Private Sub Worksheet_Activate()
    Me.ComboBox1.List = Range("MyNamedRange").Value
End Sub

Then to remove the selected item you can use this.

 If Me.ComboBox1.ListIndex <> -1 Then
        Me.ComboBox1.RemoveItem Me.ComboBox1.ListIndex
 End If

Not sure where you would put that code, really depends on what you are doing with the combobo and the values you are selecting.