If your TextBoxes came from the Drawing toolbar, then try this macro
instead...

Sub ShowTextBoxesNamesInOrder()
Dim X As Long, Z As Long, LastRow As Long
Dim O As Object, WS As Worksheet, LastSheet As Worksheet
Dim TBnames As String, TBs() As Variant
ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
Set LastSheet = Worksheets(Worksheets.Count)
LastSheet.Range("A1:D1") = Array("Sheet Name", "Name", "Top", "Index")
For X = 1 To Worksheets.Count - 1
Z = 0
Set WS = Worksheets(X)
ReDim TBs(1 To WS.Shapes.Count, 1 To 4)
For Each O In WS.Shapes
If TypeName(O.OLEFormat.Object) = "TextBox" Then
Z = Z + 1
TBs(Z, 1) = WS.Name
TBs(Z, 2) = O.Name
TBs(Z, 3) = O.OLEFormat.Object.Top
TBs(Z, 4) = X
End If
Next
If Z 0 Then
LastSheet.Cells(Rows.Count, "A").End(xlUp). _
Offset(1).Resize(Z, 4) = TBs
End If
Next
LastRow = LastSheet.Cells(Rows.Count, "A").End(xlUp).Row
LastSheet.Range("A1:D" & LastRow).Sort _
Key1:=LastSheet.Range("D2"), Order1:=xlAscending, _
Key2:=LastSheet.Range("C2"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Where did the TextBoxes that are on the sheets come from... the Control
ToolBox toolbar or the Drawing toolbar?

--
Rick (MVP - Excel)



"PA" wrote in message
...
Thank you for your help!

I am getting a runtime error 438 always in the line: ReDim TBs(1 To
WS.OLEObjects.Count, 1 To 4)

any suggestion?

thanks in advance!
PA