Word Art Object properties in VBA

[Arnie]

I have placed several Word Art objects on an Excel sheet. Is there a
way to list the Word Art objects on the sheet and then display their
properties?

So far I have been able to run a macro to manipulate the Word Art
object and that is the only way I have found to discover the name of
the object and make changes to it.

Arnie

 

[Frank Kabel]

Hi Arnie
only slightly adapting the procedure from the following site
(http://www.mvps.org/dmcritchie/excel/shapes.htm)
try
Sub getShapeProc()
'List of buttons/shapes ON THE worksheets
'based on Shawn Foley, programming group, 1999-09-10
'--
http://groups.google.com/[email protected].
mindspring.net
Dim Wks As Worksheet
Dim shp As Shape
Dim nRow As Long
Cells.Clear '-- added due to on Error
Cells(1, 1) = "Worksheet"
Cells(1, 2) = "Shape"
Cells(1, 3) = "OnAction"
Cells(1, 4) = "Hyperlink"
Cells(1, 5) = "TopLeft"
Cells(1, 6) = "BotRight"
nRow = 1
On Error Resume Next 'hyperlinks, topLeftCell, BottomRightCell
For Each Wks In ActiveWorkbook.Worksheets
For Each shp In Wks.Shapes
If shp.Type = 15 Then
nRow = nRow + 1
Cells(nRow, 1) = "'" & Wks.Name 'i.e. Worksheet
1999-01-10
Cells(nRow, 2) = shp.Name
Cells(nRow, 3) = shp.OnAction
Cells(nRow, 4) = shp.Hyperlink.Address 'additional
information
Cells(nRow, 5) = shp.TopLeftCell.Address(0, 0)
Cells(nRow, 6) = shp.BottomRightCell.Address(0, 0)
End If
Next shp
Next Wks
done: nRow = nRow + 0
End Sub

Add additional properties if you like

 

[Arnie]

Brilliant solution!!

Thank you Frank.