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 SubThis 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 SubAfter 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 SubThe 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!
Keep Learning
Chris Newman
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.