Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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.
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
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.
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 "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.
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
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.
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!