PDA

View Full Version : [SOLVED] Delete All Comboboxes



Zack Barresse
07-12-2004, 04:04 PM
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
07-12-2004, 04:41 PM
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
07-12-2004, 04:42 PM
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
07-12-2004, 04:54 PM
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
07-12-2004, 05:08 PM
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
07-12-2004, 05:23 PM
I'd just like to add the simple way, depending on usage...

Edit-Go to-Special-Objects, Delete.

Hee hee...

Daniel Klann
07-12-2004, 05:24 PM
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
07-12-2004, 10:34 PM
As Dreamboat pointed out ....


ActiveSheet.DrawingObjects.Delete

Anne Troy
07-12-2004, 10:47 PM
You mean...I was right?? ROFLMFAO!!

Ivan F Moala
07-13-2004, 04:23 AM
You mean...I was right?? ROFLMFAO!!

yes :-)

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