Search code examples
excelvbacomboboxactivex

VBA clear ActiveX combobox list

How to clear Excel ActiveX ComboBox list with VBA. I expected this code would work: 

Sheet1.OLEObjects(1).Clear

but it raises

runtime error object doesn't support this property or method.

I am puzzled because this works:

Sheet1.OLEObjects(1).Name

returning the name TempCombo.

I still fail when I try this:

Sheet1.TempCombo.Clear

It returns error:

Runtime error Unspecified error.

What else should I check?

Solution

  • If you want to clear the ActiveX ComboBox list, you may try something like this...

    Sheet1.OLEObjects(1).ListFillRange = ""

    Or more precisely this...

    If TypeName(Sheet1.OLEObjects(1).Object) = "ComboBox" Then
    Sheet1.OLEObjects(1).ListFillRange = ""
End If