[Excel VBA] How to Check Which Cells Shapes or Charts Are On | Using TopLeftCell and BottomRightCell

In Excel, you can freely place charts and shapes, but there are times when you need to know exactly which cells they are positioned over.

For example, this is useful when automating reports, controlling shape positions, or checking the relationship between data and shapes.

In this article, I will explain how to use VBA to find out which cells a shape or chart on a sheet is located on, along with practical code examples.

Code Example: Get Position Cells for All Shapes

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    MsgBox "Shape Name: " & shp.Name & vbCrLf & _
           "Top-Left Cell: " & shp.TopLeftCell.Address & vbCrLf & _
           "Bottom-Right Cell: " & shp.BottomRightCell.Address
Next

Explanation of the Code

1. ActiveSheet.ShapesRetrieves all objects (shapes, charts, images, buttons, text boxes, etc.) on the current sheet as Shape objects.
2. TopLeftCellReturns the address of the cell where the top-left corner of the object sits.Even if the shape spans multiple cells, it targets the single cell at the top-left.
3. BottomRightCellReturns the address of the cell where the bottom-right corner of the object sits.
4. Display with MsgBoxDisplays the name, top-left cell, and bottom-right cell of each shape in a message box.

Example Result

If a shape named “Rectangle 1” is placed over the range B3:D6:

Shape Name: Rectangle 1  
Top-Left Cell: $B$3  
Bottom-Right Cell: $D$6

Application: Controlling Shapes Based on Cell Position

Example: Delete shapes that are in Column A

If shp.TopLeftCell.Column = 1 Then
    shp.Delete
End If

Example: Change color only for shapes in Columns B to D

If shp.TopLeftCell.Column >= 2 And shp.BottomRightCell.Column <= 4 Then
    shp.Fill.ForeColor.RGB = RGB(200, 230, 255)
End If

Important Notes

• Corners matter: TopLeftCell and BottomRightCell depend on the exact position of the corners. It is not based on the center of the shape.
• Overlapping shapes: If shapes overlap, be aware of the Z-order (front/back) if you need to understand the layout precisely.
• Unnamed shapes: You can retrieve information even for shapes without custom names. shp.Name will automatically use names like “Rectangle 1”.

Summary

Using this method, you can retrieve location information for all shapes and charts on a sheet and process them dynamically. This is useful for automating report templates, checking for placement errors, or creating auto-alignment tools.