It worked for me.
However, I declared all variables and entered some text in all of the shapes.
(and limited the test to one sheet in xl2002)

Error 2042 is xlErrNA. I don't know why you would get that.
I have suspicions that you are using xl2007 and/or Vista (both are betas).
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"LurkingMan"
wrote in message
Hi All:
I'm new to VB and Excel, so I may have simply blundered.
I'm trying to read text out of a shape that's a text box. ( Is that the right
way to describe it? It seems multiple different things are called text boxes.)

I use a method that works _sometimes_ and sometimes gives Error 2042. It is
s.TextFrame.Characters.Text, where s is tested to be a shape whose .Type is
msoTextBox.
(Side question: How do I find the meaning of Error 2042 in this context?
Searching for "Error 2042" excel gives 9,000 hits wth nothing on the first
few pages seeming relevant.)

My problem happens on a brand new workbook that has only a few
textboxes (some of them grouped) on a worksheet. I succeed in visiting all
existing textboxes and can display the name of each. So, what have I done
wrong in the following code to make s.Name work and
s.TextFrame.Characters.Text not work for the same s?


'Show each sheet of all open workbooks and call FindTB on it.
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 increasingly desperate testcode
If s.Type = msoTextBox Then MsgBox (s.TextFrame.Characters.Text)
' Getting text from a shape that's a textbox always works here.

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 ^^ that worked in the calling function
' gets Error 2042 here and xx is empty,
' but ONLY in a recursive call to FindTB...
MsgBox (s.Name)
' even though Name is the name of a text box.

'MsgBox (s.TextFrame.Characters.Text)
' and uncommenting the line above gets Error 13 Type mismatch

ElseIf s.Type = msoGroup Then
For Each x In s.GroupItems
FindTB x
Next x
End If
End Sub
--
In theory, there is no difference between theory and practice.
In practice, there is no similarity.