<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Deleting Shapes - A Warning

Published on
4,158 Points
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month