Excel VBA Tips: Get Shape Properties

By

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