How To Keep Track Of Your Shapes Created With VBA Code
Lately, I have been writing a lot of VBA code dealing with the creation of shapes and managing them. I want to share one powerful little trick with you that makes the management part a lot easier. I’ve used this technique in projects such as the Guru Tab PowerPoint add-in and the Tickmark Excel add-in.
Let’s walk through a very simple example and you’ll better understand the types of scenarios I’m talking about.
How You Might Use VBA & Shapes In The Real World
For this example, I’m going to use my most frequently used shape macro of all time called RedBox. I use this macro almost every day to add a red box around selected cells that I want to bring attention to on a given spreadsheet. It looks a little something like this:
Sub RedBox() 'PURPOSE: Create a Red Box to Highlight Cell Selection 'SOURCE: www.TheSpreadsheetGuru.com Dim Box As Shape 'Create a Red Box based on Cell Selection On Error GoTo Select_Cell: Set Box = ActiveSheet.Shapes.AddShape( _ Type:=msoShapeRectangle, _ Left:=Selection.Left, _ Top:=Selection.Top, _ Width:=Selection.Width, _ Height:=Selection.Height) On Error GoTo 0 'Format the Red Box Box.Fill.Visible = msoFalse Box.Line.Visible = msoTrue Box.Line.ForeColor.RGB = RGB(255, 0, 0) Box.Line.Weight = 2.25 Exit Sub 'ERROR HANDLER Select_Cell: MsgBox "Select a cell in order to use this button." End Sub
Now, what if I want to write another macro that removes all the red boxes from the spreadsheet? Most people would probably write some VBA that loops through all the shape objects on the spreadsheet and deletes them.
Sub DeleteAllShapes() 'PURPOSE: Remove All Shape Objects From The Active Worksheet (Excludes Charts/Comments) 'SOURCE: www.TheSpreadsheetGuru.com Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type <> msoChart And shp.Type <> msoComment Then shp.Delete Next shp End Sub
This might work in most cases, but what if you added others shapes in addition to the red boxes? Now all your shape objects have been wiped out!
Tagging, A Simple Solution To Track Your Shapes
My strategy for managing shapes within my VBA code is to manipulate the shape name. What I do is predetermine a unique tag to add to either the beginning or the end of the newly created shape’s name. This way I can bucket the shapes based on my tags.
So let’s take that same “Create a Red Box” macro and add the tag “_RedBox” to the end of the shape name so we can easily track it later on.
Sub RedBox() 'PURPOSE: Create a Red Box to Highlight Cell Selection 'SOURCE: www.TheSpreadsheetGuru.com Dim Box As Shape 'Create a Red Box based on Cell Selection On Error GoTo Select_Cell: Set Box = ActiveSheet.Shapes.AddShape( _ Type:=msoShapeRectangle, _ Left:=Selection.Left, _ Top:=Selection.Top, _ Width:=Selection.Width, _ Height:=Selection.Height) On Error GoTo 0 'Rename Box with Tag Box.Name = Box.Name & "_RedBox" 'Format the Red Box Box.Fill.Visible = msoFalse Box.Line.Visible = msoTrue Box.Line.ForeColor.RGB = RGB(255, 0, 0) Box.Line.Weight = 2.25 Exit Sub 'ERROR HANDLER Select_Cell: MsgBox "Select a cell in order to use this button." End Sub
After adding over 100 red boxes and 100 circle shapes to the spreadsheet, lets say we decide we want to make all the red boxes blue instead. Since we have both circle and box shapes throughout the spreadsheet, we need to find a way to specifically target just the red boxes.
Let’s assume our Red Boxes were created with a macro that added the tab “_RedBox” to the end of the shape name. We can easily loop through all the shapes on the spreadsheet and zone in on a specific tag. Below is an example macro of how you could target just the red boxes and turn them into blue boxes by using my tagging methodology.
Sub RedBox_to_BlueBox() 'PURPOSE: Convert All Red Boxes To Blue Boxes Dim shp As Shape Dim Tag As String 'What is the Tag Name for the Red Box Shapes? Tag = "_RedBox" 'Loop through each shape in ActiveSheet For Each shp In ActiveSheet.Shapes 'Test for Tag Name at the end of Shape Name If Right(shp.Name, Len(Tag)) = Tag Then 'Change Line Color To Blue shp.Line.ForeColor.RGB = RGB(0, 0, 255) 'Change Shape Name Tag shp.Name = WorksheetFunction.Substitute(shp.Name, Tag, "_BlueBox") End If Next shp End Sub
The end result has our green circles remaining untouched and the red boxes converted into blue boxes! Now, this is a very basic example, but hopefully, it illustrates the logic behind how you can identify objects.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Determining The ActiveShape On Your Spreadsheet With VBA
What This Does There are times when you would like to know information about the currently selected shape on your...
Adjust Object Positioning Property To All Shapes & Images On Your Excel Worksheet
What This Does Ever have an instance where you have a TON of shapes or images on your spreadsheet and...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.