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,
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Last edited by Aussiebear; 04-29-2023 at 09:49 PM. Reason: Adjusted the code tags
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
"All that's necessary for evil to triumph is for good men to do nothing."
Hi Zack,
They are all members of the Sheet's Shapes Collection so try something like
This should delete all your controls - if you only want particular types you'll need some selection logicFor Each s in activesheet.shapes s.delete next
Last edited by Aussiebear; 04-29-2023 at 09:50 PM. Reason: Added code tags
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
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?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
I'd just like to add the simple way, depending on usage...
Edit-Go to-Special-Objects, Delete.
Hee hee...
~Anne Troy
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 ....
ActiveSheet.DrawingObjects.Delete
Last edited by Aussiebear; 04-29-2023 at 09:51 PM. Reason: Adjusted the code tags
Kind Regards,
Ivan F Moala From the City of Sails
You mean...I was right?? ROFLMFAO!!
~Anne Troy
yes :-)Originally Posted by Dreamboat
That's just to delete everything .....
Kind Regards,
Ivan F Moala From the City of Sails