Deleting Shapes - A Warning

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
This article describes a serious pitfall that can happen when deleting shapes using VBA.

First of all, credit to expert byundt who first pointed this out to me.


In Excel it's common practice to add a shape like a rounded rectangle and assign a macro to it so that it will work like a button to execute certain code when it is "clicked". Callout shapes like the one above are also common along with charts and textboxes. The problem is that there are other objects which fall under the "shapes" umbrella that you may not be aware of and if you aren't cautious they can be deleted causing serious consequences.


The following is a slightly modified version of what was explained by byundt.


VBA Code to Delete Shapes

Code like the following snippet should never be used! While it will delete some objects that are obviously shapes, it will also delete useful shape objects like data validation dropdowns, cell comments, Forms and Control ToolBox command buttons, charts and explanatory textboxes. The data validation dropdowns are deleted irreversibly (you can never add another one to the worksheet). To recover data validation dropdowns, you must insert another worksheet and recreate the content, then delete the "shapeless" original.


Sub DeleteUnwantedShapes()
    ' DO NOT USE THIS CODE
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        ' Keep shapes that start with "Keep" and delete the rest
        If Left(shp.Name, 4) <> "Keep" Then shp.Delete
    Next
End Sub


Example Spreadsheet - Delete!

If you download the attached workbook you'll find an example of what can happen if you aren't careful when you delete shapes.  Be sure to "Enable Content" and "Enable Macros" if you are prompted.

Delete shapes.xlsm


Sheet1 contains: 

  • A Data Validation list colored light blue near cell B2
  • Two green shapes named "Keep1" and "Keep2" 
  • A red shape named "Delete me"

Before you do anything else, click on the Data Validation list and you'll see that it contains four values; "this","is","a" and "test". 


So now let's assume that for some reason you want to delete the red shape via code. 


Open the code editor (Alt + F11) and find "Module1" under Modules.


Here you'll find two macros; "DeleteUnwantedShapes" and "DeleteUnwantedShapesImproved".


The Wrong way

The "DeleteUnwantedShapes" looks perfectly innocuous in that you only see three shapes on the sheet and only one of them doesn't begin with "Keep", so what could go wrong? Well, if you run it you'll find out! What happens is that it deletes the red shape OK, but the data validation list no longer works, and to make it worse you can't easily recreate it and instead you need to follow the instructions mentioned above, ie inserting a new sheet and recreating everything.


The problem with the "DeleteUnwantedShapes" is that the <> (not equal) comparison is too broad and unintended "shapes" get deleted. 


The Right Way

Sub DeleteUnwantedShapesImproved()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        ' Delete the "Delete Me" shape
        If Left(shp.Name, 6) = "Delete" Then shp.Delete
    Next
End Sub


Be Specific!

To avoid unintended consequences, always specify what you DO want to delete (as is done in "DeleteUnwantedShapesImproved") rather than what you DON'T want to delete.  The key here is to be specific and target the shapes you want to delete.


Finally

If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!

3
3,766 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.