How To Keep Track Of Your Shapes Created With VBA Code

Tracking Excel Shape Objects

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 everyday 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

 
Modifying Different Types of Excel Shape Objects

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 shapes 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.

Modifying Different Types of Excel Shape Objects

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 to 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.

Modifying Different Types of Excel Shape Objects

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)