Excel VBA Tips: Get Shape Properties

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.


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!



5 Responses to “Excel VBA Tips: Get Shape Properties”
  1. goggala says:

    How do I show the name of the shape in the same page a cell

    • submeg says:

      If you look at that code, it will list all the different shapes on your page, so e name will be visible

      • goggala says:

        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?

  2. MrRugnir says:

    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

    • sleekattac007 says:

      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”….

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

  • Views:

    • 327,468 hits
  • submeg.fm: 30,377 songs 6,160 artists

Get every new post delivered to your Inbox.

Join 850 other followers

%d bloggers like this: