Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Shape References

7 June 2019

Welcome back to the VBA blog!  This week we are going to cover something we’ve had to do in one of our consulting jobs. It is a little niche, but it may be a good source of inspiration for some of us.

 

We had to create a large number of shapes that displayed the text value of the cell each shape covered. Sometimes these cells and / or shapes would move. We thought that going through the shapes manually and relinking each shape to its respective cell reference would prove to be quite time consuming. So, we wrote a small macro to help us!

Say we have the following worksheet layout: 

The next step is to create some shapes and position them over some of the cells with text in them. 

We can then run the following macro that we wrote:

Sub Shapes ()

 

Dim sh As Shape

Dim shformula As Variant

For Each sh In ActiveSheet.Shapes

 

    Set shformula = ActiveSheet.Shapes.Range(Array(sh.Name))

   

    shformula.Select

    Selection.Formula = "=" & sh.TopLeftCell.Address

 

Next sh

End Sub

The first three lines of code begin the macro and defines our variables that the rest of the code will reference.

The 4th line of code:

For Each sh In ActiveSheet.Shapes

starts a loop for the code to run for every shape. The 5th line:

    Set shformula = ActiveSheet.Shapes.Range(Array(sh.Name))

defines the variable shformula as the object formula that we are defining. The 6th line selects the variable shformula, and the 7th line assigns the reference of the cell that is located on the top left corner of the shape, to the shape’s formula.

Curiously the macro does not work if you combine the 6th and 7th lines of code to:

    shformula.Formula = "=" & sh.TopLeftCell.Address

the macro just returns with an error when we execute it in this form.

After running the macro, the shapes now have cell references the respective text values: 

We can also reallocate the shapes to new positions:

Then re-run the macro to update their contents:

That’s it for this week! Come back next week for more VBA tips!

Newsletter