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
SUBMEG 
How do I show the name of the shape in the same page a cell
LikeLike
If you look at that code, it will list all the different shapes on your page, so e name will be visible
LikeLike
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?
LikeLike
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.
LikeLike
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”….
LikeLike