Re: finding text in all textboxes

In article <826D25D6-7739-4DBA-9770-13C9E6C3DE82@[EMAIL PROTECTED]
>,
 LurkingMan <LurkingMan@[EMAIL PROTECTED]
> wrote:

> Hi All:
> I'm new to VBA and Excel and have probably made an elementary error, or 
> several. 

I don't see any errors, and the code runs without error for me in 
workbooks with both standalone and grouped textboxes.

However, you say the error occurs at the snippet
 
   theText = x.TextFrame.Characters.Text

yet that snippet doesn't appear anywhere in your code, so there may be 
something else going on...

Note that you really don't need to activate anything:

    Public Sub SearchAllTBs2()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim sh As Shape
        
        For Each wb In Workbooks
            For Each ws In wb.Worksheets
                For Each sh In ws.Shapes
                    GetTBText sh
                Next sh
            Next ws
        Next wb
    End Sub

    Public Sub GetTBText(sh As Shape)
        Dim shGroupItem As Shape
        Select Case sh.Type
            Case msoGroup
                For Each shGroupItem In sh.GroupItems
                    GetTBText shGroupItem
                Next shGroupItem
            Case msoTextBox
                MsgBox sh.Name & vbNewLine & sh.TextFrame.Characters.Text
            Case Else
                'do nothing
        End Select
    End Sub



> 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