You didn't say where to display the ordered names at, so I simply added a
new worksheet at the end of your list and listed them there (along with the
worksheet Name they are on, the TextBox's Top value on that sheet, and the
sheet's Index value which was used during the sort process)... you can
delete this worksheet after you are done with it.

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.OLEObjects.Count, 1 To 4)
For Each O In WS.OLEObjects
If TypeName(O.Object) = "TextBox" Then
Z = Z + 1
TBs(Z, 1) = WS.Name
TBs(Z, 2) = O.Name
TBs(Z, 3) = O.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)



"PA" wrote in message
...
Hi all,

I was asked to help with this problem and I am struggling to find a
quick way to do it.

I need to retrieve the name of all text boxes in a spreadsheet in the
same order they appear from top to bottom. I have around 10 worksheets
each with 12 - 20 text boxes...

Thanks in advance.

PA