get-digital-help.com

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 executes a macro.

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. Click "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 click on your workbook name (Locate a shape.xlsm) in the Project Explorer.
4. Click "Insert" on the menu.
5. Click "Module" to create a module in your workbook.
6. Paste VBA code to code window, see above image.

Back to top

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

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?

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