Public Function GetShapeProps()
Dim rngShapeRef As Range
Dim wks As Worksheet
Dim wks1 As Worksheet
Dim sh As Shape
Dim s as String

For Each wks In ThisWorkbook.Worksheets
wks.Activate
Debug.Print "ActiveSheet: " & wks.Name
Dim intCounter As Integer
intCounter = 1
For Each wks1 In ThisWorkbook.Worksheets
For Each sh In wks1.Shapes
s = sh.ControlFormat.LinkedCell
if instr(1,s,"!",vbTextCompare) then
Set rngShapeRef = Range(s)
else
set rngShapeRef = sh.Range(s)
end if
Debug.Print intCounter & ": " & sh.AlternativeText & _
": " & rngShapeRef.Address(External:=True)
intCounter = intCounter + 1
Next
Next
Debug.Print ""
Next

End Function

--
Regards,
Tom Ogilvy


"Troy" wrote in message
ps.com...
Hmmmm....I think I answered my own question on this last one:

Activate the sheet I'm checking the shapes collection for. That way,
any unqualified reference will return the appropriate sheet (the
currently activated one, which happens to be the same sheet the control
is located on).

Although it still sucks that I have to activate each sheet as I go
along, it's at least shorter than looping the names collection!


Troy wrote:
I almost fogot! How would you improve the code's accuracy without
checking other sources like the names collection?