Locate a shape in a workbook

This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The image above shows a worksheet that allows you to specify a value in cell B1 and a button that runs a macro.

1. Locate a shape

The macro iterates through all worksheets in the workbook and then iterates through all shapes in each worksheet to find a shape with the given text value.

The macro then activates the worksheet containing that particular shape, selects the shape, and moves to the location of that shape.

How to use the workbook

1. Enter the number you want to look for in cell B1.
2. Press with left mouse button on "Find shape" button shown in the image above.
   
3. The macro finds the shape and goes to that worksheet and location.

Back to top

VBA Code

'Name macro
Sub FindShape()

'Dimension variables and declare data types
Dim sht As Worksheet
Dim shp As Shape

'Iterate through worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through shapes in each worksheet
    For Each shp In sht.Shapes

        'Check if shape text meets condition in cell B1
        If shp.TextFrame.Characters.Text = Worksheets("Sheet1").Range("B1").Value Then
   
            'Activate worksheet
            sht.Activate

            'Select shape
            shp.Select

            'Scroll to row containing shape
            ActiveWindow.ScrollRow = shp.TopLeftCell.Row

            'Scroll to column containing shape
            ActiveWindow.ScrollColumn = shp.TopLeftCell.Column

            'Exit macro
            Exit Sub
        End If
    Next shp
Next sht
End Sub

Back to top

Where to put the code?

If you want to use the macro in your workbook you need to follow these steps.

1. Copy above VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Double press with left mouse button on your workbook name (Locate a shape.xlsm) in the Project Explorer.
4. Press with left mouse button on "Insert" on the menu.
5. Press with left mouse button on "Module" to create a module in your workbook.
6. Paste VBA code to code window, see above image.

Get the Excel file

Locate-a-shape.xlsm

Back to top

2. Locate an Excel Table or a named range

To find a specific Excel Table in a Workbook is easy as long as you know the name of it. Type the name in the name box at top left corner of your Excel window, see image above.

This will take you instantly to the Excel Table even if it is located in another worksheet. If you don't know the name of the Excel Table I recommend you use the "Name  Manager" to find the Excel Table you are looking for.

1. Go to tab "Formulas" on the ribbon.
2. Press with mouse on "Name Manager" button.
   
3. The "Name Manager" shows the names of all Excel Tables and named ranges in your workbook along with the worksheet and cell references.

The VBA code below demonstrates how to locate an Excel Table or a named range programmatically.

'Name macro
Sub Macro1()

'Go to named range named "BBC", this will also work with cell references and Excel Tables
Application.Goto Reference:="BBC"
End Sub

Where to put the code?

Back to top

3. Locate a shape/chart by name

The following macro iterates through all shapes and charts in your workbook looking for the name "Chart 1". When found it goes to that particular worksheet and to the exact location of the shape or chart.

'Name macro
Sub FindChart()

'Dimension variables and data types
Dim sht As Worksheet
Dim shp As Shape

'Iterate through all worksheets in active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through all shapes in worksheet
    For Each shp In sht.Shapes

        'Check if name is "Chart 1"
        If shp.Name = "Chart 1" Then

            'Activate worksheet
            sht.Activate

            'Select chart
            shp.Select

            'Go to row that matches the chart location
            ActiveWindow.ScrollRow = shp.TopLeftCell.Row

            'Go to column that matches the chart location
            ActiveWindow.ScrollColumn = shp.TopLeftCell.Column

            'End macro
            Exit Sub
        End If

    'Continue with next shape
    Next shp

'Continue with next worksheet
Next sht
End Sub

Where to put the code?

4. List all names of shapes and charts in a workbook

This macro creates a list in worksheet "List" of all shape and chart names in your workbook. It also lists their corresponding cell address and what worksheet they are on.

The list is created in column A and B in worksheet "List", you can change this location, if you prefer, in the macro below.

'Name macro
Sub ListShapes()

'Dimesnion variables and declare data types
Dim sht As Worksheet
Dim shp As Shape, r As Integer

'Save 0 (zero) to variable r
r = 0

'Iterate through each worksheet in the active workbook
For Each sht In ActiveWorkbook.Worksheets

    'Iterate through each shape
    For Each shp In sht.Shapes

        'Save shape name to column A based on variable r
        Worksheets("List").Range("A1").Offset(r, 0) = shp.Name

        'Save shape name to column B based on variable r
        Worksheets("List").Range("B1").Offset(r, 0) = sht.Name & "!" & shp.TopLeftCell.Address

        'Add 1 to the number saved in variable r
         r = r + 1

    'Continue with next shape
    Next shp
Next sht
End Sub

Where to put the code?

Back to top

Macro category

Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list […]

Add values to a regular drop-down list programmatically
In this tutorial, I am going to show you how to add values to a drop down list programmatically in […]

Add values to a two-dimensional table based on conditions [VBA]
This article demonstrates how to place values automatically to a table based on two conditions using a short macro. Cell […]

Excel categories

Comments (1)