Mac Forum / Applications / Excel / May 2008

Hi All:
I'm new to VBA and Excel and have probably made an elementary error, or
several.
I'm trying to retreive  text from all the textboxes (the kind added by the
drawing toolbar) on all the sheets of multiple open workbooks.

I think I've gotten the iteration right, but in the two Subs below, the code
snippet
theText = x.TextFrame.Characters.Text  
seems to not work when x is in a group, even though I'm checking to verify
that x is in fact a text box.

Any help is appreciated. Here's code demonstrating the problem/my confusion.

'Visit all sheets in all open workbooks and call FindTB on each
Sub SearchAllTBs()
   For i = 1 To Workbooks.Count
       Workbooks(i).Activate
       For j = 1 To Sheets.Count
           Worksheets(j).Activate
           For Each s In ActiveSheet.Shapes

'Some testcode: Getting text from a shape that's a textbox always works here.
               If s.Type = msoTextBox Then
                   xx = s.TextFrame.Characters.Text
                   MsgBox (xx)
               End If

              FindTB s
           Next
       Next j
   Next i
End Sub

'Visit all (shape)text boxes on the active sheet,
'even if they're in a group
Sub FindTB(s)
   If s.Type = msoTextBox Then
       xx = s.TextFrame.Characters.Text
'The same line  ^^ gets Error 2042 here...
       MsgBox (s.Name)  
'even though the object seems to be the expected text box.

   ElseIf s.Type = msoGroup Then
       Set gs = s.GroupItems
       For i = 1 To gs.Count
           Set x = gs.Item(i)    'so x must be somehow wrong?
           FindTB x
       Next
   End If
End Sub