finding text in all textboxes

[LurkingMan]

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

 

[JE McGimpsey]

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

 

[LurkingMan]

HI JE McGimpsey:
Thanks for taking the time to respond to my question.
First off I should have said the code fragment .TextFrame.Characters.Text,
which I use twice.

My code runs for you??
Your sample code gets a runtime error on my laptop at the same place my code
has a problem.
sh.TextFrame.Characters.Text

Is it possible I'm fighting versioning or installation issues? I tried it on
a co-worker's machine with the same result. I'm on XP with service pack 2 and
excel 2003.

I'm stumped about how to proceed when the code runs elsewhere. Any
suggestions at all?

 

[JE McGimpsey]

My code runs for you??
Yes.

Your sample code gets a runtime error on my laptop at the same place my code
has a problem.
sh.TextFrame.Characters.Text

*WHICH* run-time error?

Is it possible I'm fighting versioning or installation issues? I tried it on
a co-worker's machine with the same result. I'm on XP with service pack 2 and
excel 2003.

Well, you're posting in an XL for Macintosh newsgroup, but as far as I
can tell, it should work fine in XL03 as well.

I'm stumped about how to proceed when the code runs elsewhere. Any
suggestions at all?

Is it only in one workbook? Does the same thing happen in a brand new
one?

 

[LurkingMan]

HI JE McGimpsey:
Jeez I don't know how I wound up posting in a different group than I aimed
at, doh.
Since my code has the problem on a brand new spreadsheet with nothing but a
few textboxes on it, I'm going to move my questionto the right discussion
group, because I fear I am dealing with some stupid versioning problem.
Thanks for your help & sorry for the confusion.