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?
--
In theory, there''''s no difference between theory and practice.
In practice, there''''s no similarity.
"JE McGimpsey" wrote:
> 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
>
|
