Remove all items from an ActiveX control on a sheet
 

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.

Remove all items from an ActiveX control on a sheet
 

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





"quartz" wrote in message
...
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.

Remove all items from an ActiveX control on a sheet
 

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?

"Chip Pearson" wrote:

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





"quartz" wrote in message
...
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.

Remove all items from an ActiveX control on a sheet
 

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

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
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?

"Chip Pearson" wrote:

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





"quartz" wrote in message
...
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.

Remove all items from an ActiveX control on a sheet
 

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"quartz" wrote in message
...
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?

"Chip Pearson" wrote:

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





"quartz" wrote in message
...
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.