<

Deleting Shapes - A Warning

Published on
4,589 Points
789 Views
3 Endorsements
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
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
Comment
Author:Martin Liss
0 Comments

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month