CODEWITHSUNDEEP
excelvba
marillion
marillion

Reputation: 11170

Delete all shapes in excel worksheet except form controls

I have an excel worksheet where a macro tied to a button draws dynamic shapes based on the user input parameters in the worksheet.

I am trying to write a new macro to clean the sheet, or in other words delete all shapes in the worksheet.

I tried using the code below, and it indeed deletes all shapes, however button form controls also get deleted in the process. Is there an easy way to only get rid of the shapes (arrows, textboxes, ellipses etc.) in the worksheet? Thanks a bunch!!!

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp

End Sub

Upvotes: 14

Views: 69335

Answers (2)

LondonJustin
LondonJustin

Reputation: 73

It sounds like you just want to delete drawing objects.

Building on Jamie Bull's excellent advice, firstly here's the updated link to the relevant page in Ron de Bruin's website:

Delete or Hide Objects/Controls on a worksheet

and secondly, you may find that this single line will do the trick:

ActiveSheet.DrawingObjects.Delete

Upvotes: 0

Jamie Bull
Jamie Bull

Reputation: 13519

To delete autoshapes and textboxes only you can use:

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoAutoShape Or Shp.Type = msoTextBox Then Shp.Delete
Next Shp

End Sub

Alternatively you can work the other way around and specify the types not to delete. You can use the enumerated types but it's more readable to use the type names. The following snippet will delete everything apart from Form Controls and OLE control objects.

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl) Then Shp.Delete
Next Shp

End Sub

A full list of MSO Shape Types. http://msdn.microsoft.com/en-us/library/office/aa432678(v=office.12).aspx

Ron de Bruin has a good collection of snippets which may be relevant to anyone else coming across this question. http://www.rondebruin.nl/controlsobjectsworksheet.htm

Upvotes: 21

Related Questions