VBA clear ActiveX combobox list

Question

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?

I think it must be Sheet1.OLEObjects(1).Object.Clear. But also Sheet1.TempCombo.Clear works for me without throwing an error. Sheet1.TempCombo.List = Array() is another way to remove all items from the list. • I cannot reproduce your issue. There must be something else causing this error. — Pᴇʜ
@Pᴇʜ you are right! .Clear should actually work. Did you check if you're sheet/combobox -name is correct? — J.schmidt
@J.schmidt Sheet name must be correct otherwise Sheet1.OLEObjects(1).Name would not give the correct name. Therefore also the ComboBox name is proof. — Pᴇʜ
@Pᴇʜ I have found out that Sheet1.TempCombo.Clear will fail unless the property .ListFillRange = "". This is experienced by other users stackoverflow.com/a/51425477/1903793 — Przemyslaw Remin
@PrzemyslawRemin Strange it worked for me. But I never used the .ListFillRange in my test. I added items with TempCombo.AddItem "test". So thanks for the follow up. — Pᴇʜ

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2019-01-18T13:12:38

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