Delete All Comboboxes

[Zack Barresse]

Hi,

I have about 300 combo boxes (ActiveX) on one sheet. Is there a fast loop to go through the entire sheet and delete all of the combo boxes?

Better yet, all controls? I'm struggling on this one. Thanks.

[Daniel Klann]

Hi,

How about this?

Sub DeleteAllControls()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        obj.Delete
    Next obj
End Sub

If you just wanted to delete comboboxes then try:

Sub DeleteComboboxesOnly()
    Dim obj As OLEObject
    For Each obj In ActiveSheet.OLEObjects
        If obj.progID = "Forms.ComboBox.1" Then obj.Delete
    Next obj
End Sub

HTH
Dan

[jamescol]

I'm not 100% positive, but I think you can remove programatically only controls that were created at run time. VBA can't remove controls created in design mode. Never worked with controls on a spreadsheet, so maybe there is another way. Maybe some sort of Copy - Paste Special?

From the Help File:

Remove Method


Removes a member from a collection; or, removes a control from a Frame, Page, or form.

Syntax

object.Remove( collectionindex)

The Remove method syntax has these parts:

Part Description
object Required. A valid object.
collectionindex Required. A member's position, or index, within a collection. Numeric as well as string values are acceptable. If the value is a number, the minimum value is zero, and the maximum value is one less than the number of members in the collection. If the value is a string, it must correspond to a valid member name.


Remarks

This method deletes any control that was added at run time. However, attempting to delete a control that was added at design time will result in an error.




James

[TonyJollans]

Hi Zack,

They are all members of the Sheet's Shapes Collection so try something like

For Each s in activesheet.shapes
    s.delete
next

This should delete all your controls - if you only want particular types you'll need some selection logic

[Zack Barresse]

Thanks!

Daniel's & Tony's solutions work perfectly! Thank you very much!

A question in regards to the different syntax's used in those two solutions. (So I can get this straight) Combo boxe's are part of both OLEObjects & the Shapes Collection? If so, maybe I misunderstood the difference, can you guys explain it to me in layman's terms?

[Anne Troy]

I'd just like to add the simple way, depending on usage...

Edit-Go to-Special-Objects, Delete.

Hee hee...

[Daniel Klann]

Paraphrasing from the help file, plus my own input:

The Shapes collection is a collection of all shape objects that exist in the drawing layer e.g. autoshape, freeform, OLE Object, picture. ActiveX controls are OLE objects and therefore included as part of the Shapes collections.

The OLEObjects collection is a collection of all ActiveX controls or embedded objects (e.g. an embedded Powerpoint presentation) on a worksheet.

Therefore, ActiveX controls are members of both the Shapes collection AND the OLEObjects collection.

HTH,
Dan

[Ivan F Moala]

As Dreamboat pointed out ....

ActiveSheet.DrawingObjects.Delete

[Anne Troy]

You mean...I was right?? ROFLMFAO!!

[Ivan F Moala]

You mean...I was right?? ROFLMFAO!!

yes :-)

That's just to delete everything .....