Excel VBA Tips: Get Shape Properties

Lately, I have been working with shapes in excel – moving, creating, making them invisible and resizing them. This seems easy, but because excel codes the location of an object from the top left of cell A1, it makes it very difficult to work out exactly where the object is on the page. I went searching online when I came across this vba macro written by Dave Hawley. It runs through all the shapes on the active worksheet, and pastes their properties on a new worksheet. Very handy!

Sub GetShapeProperties()

    Dim sShapes As Shape, lLoop As Long
    Dim wsStart As Worksheet, WsNew As Worksheet 

    ''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''
    ''''''''''Dave Hawley www.ozgrid.com''''''''''''
    ''''''''''''''''''''''''''''''''''''''''''''''''

    Set wsStart = ActiveSheet
    Set WsNew = Sheets.Add  

    'Add headings for our lists. Expand as needed

    WsNew.Range("A1:F1") = _
     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top")

    'Loop through all shapes on active sheet

    For Each sShapes In wsStart.Shapes

        'Increment Variable lLoop for row numbers

        lLoop = lLoop + 1

        With sShapes

            'Add shape properties

            WsNew.Cells(lLoop + 1, 1) = .Name
            WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.Name
            WsNew.Cells(lLoop + 1, 3) = .Height
            WsNew.Cells(lLoop + 1, 4) = .Width
            WsNew.Cells(lLoop + 1, 5) = .Left
            WsNew.Cells(lLoop + 1, 6) = .Top
            'Follow the same pattern for more

        End With

    Next sShapes    

    'AutoFit Columns.

    WsNew.Columns.AutoFit

End Sub

Keep this one bookmarked, very useful module right there! Has saved me a stack of time and makes troubleshooting very easy!

Come check out the directory for the rest of my excel tips!

Submeg

IT

Posted by:submeg

Musician, Writer and Inspirer. I discuss the projects I am working on, the services I provide and my general thoughts.

5 replies on “Excel VBA Tips: Get Shape Properties

      1. the above code works very nice. I tried it. but I do not want in a list. When I click on the shape, name of shape show in cell A1. In another click when the different shape, I want to show the cell a1. Could you please help me?

        Like

  1. how do i refrence these shapes in vba code itself? i’ve been trying to get them to stick into the corner of the worksheet wherever the user moves the window. i’ve been trying to use .Row = ActiveWindow.Scrollrow
    .Column = ActiveWindow.ScrollColumn
    Thanks.

    Like

    1. if you are trying to freeze the shapes , just select all the shapes, right click, choose properties and choose”dont move or size with cells”….

      Like

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s