Delete All Comboboxes

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. :)

Hi,

How about this?

Code:

---

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:

Code:

---

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

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

Hi Zack,

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

Code:

---

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

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?

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

Edit-Go to-Special-Objects, Delete.

Hee hee...

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

As Dreamboat pointed out ....

Code:

---

ActiveSheet.DrawingObjects.Delete

---

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

Quote:

---

Originally Posted by Dreamboat

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

---

yes :-)

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