Remove all items from an ActiveX control on a sheet

[Guest]

How do you remove all items from an ActiveX control on a spreadsheet?

Is there an equivalent to "RemoveAllItems"?

For example:

Dim ctrlX As OLEObject
Set ctrlX = ActiveSheet.OLEObjects("ComboBox1")
<<<<Remove All Items Code Needed Here>>>

Also, how do you retrieve the current selection from this control?

Thanks in advance for your assistance.

 

[Chip Pearson]

Try something like the following:

Dim Obj As OLEObject
For Each Obj In ActiveSheet.OLEObjects
Obj.Delete
Next Obj


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Sorry for the confusion, Chip, I wasn't clear...I don't want to delete the
control itself, but only the entries in the dropdown list! How can I do this?

 

[Tom Ogilvy]

how are the entries placed in the combobox to begin with?

If by listfillrange, then set that to ""
if by additem or similar, then Clear

Sub Add()
For i = 1 To 10
ActiveSheet.ComboBox1.AddItem "Item" & i
Next
End Sub

Sub Remove()
ActiveSheet.ComboBox1.Clear
End Sub

 

[Guest]

Thanks Tom, that was what I was after.

My entries were added programmatically on the fly and are dynamic, but I
needed to start with a clean list before each update.