why does s.TextFrame.Characters.Text work only sometimes?

L

LurkingMan

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
 
J

Jim Cone

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
 
L

LurkingMan

Hi Jim:
Thanks for the response.
I tried your restrictions and entered text in all boxes and limited my test
to one sheet. Same problem. I am using Excel 2003 and XP, but I have sp3 for
XP, so i tried it on a coworker's machine, with Excel 2003, XP and sp2. Same
problem.

Are there any common-knowledge guidelines for when vba works on one system
but not another, or am I just in versioning hell?
 
J

Jim Cone

I don't believe the Excel version is an issue here.
It probably lies more with differences in the shapes/textboxes used
and any variation between your posted code and your actual code.

I had two autoshapes (grouped) and a single textbox.
All had text/captions entered. Until I added the text/captions
the whole thing bombed.

Make sure your code is in a standard module - not the ThisWorkbook
module and not a module behind a sheet.
"Option Explicit" as the first line in your module is strongly recommended.
(no quote marks)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"LurkingMan" <[email protected]>
wrote in message
Hi Jim:
Thanks for the response.
I tried your restrictions and entered text in all boxes and limited my test
to one sheet. Same problem. I am using Excel 2003 and XP, but I have sp3 for
XP, so i tried it on a coworker's machine, with Excel 2003, XP and sp2. Same
problem.

Are there any common-knowledge guidelines for when vba works on one system
but not another, or am I just in versioning hell?
 
L

LurkingMan

I took your advice about option explicit and also minimized my test case as
you described. I tried my code on multiple machines and got the same error,
so you are right about it not being a versioning issue.
Still, no progress. Since I'm new to this i might be assuming something, so
I'm going to describe my steps in exhausting detail.

Here are my exact steps:
I create a new workbook and since it has 3 sheets I delete sheet2 and sheet3.
I use the drawing toolbar to add a textbox.
I copy it twice and add box1 box2 and box3 as text.
I group box 2 and 3by selecting them with shift-click and then
right-clicking and choosing group from the menu.
I hit select all and verify that's all that's on the worksheet.
I hit alt f11 and right-click on Microsoft excel objects in the vba project
pane and select "insert module"

I paste the code shown below into the code window and run.
I get a Message box with Text Box1box1
I click OK and get a message box with Text Box2box2
I click OK and get a message box with Group 4
I click OK and get run time error 13 type mismatch
When i click debug the line
xx = s.TextFrame.Characters.Text
is pointed at.

When i look at the call stack I see
VBAProject.Module1.FindTB
VBAProject.Module1.FindTB
VBAProject.Module1.SearchAllTBs

When I reexecute, I set a break point at the call toFindTB x that's inside
FindTB and add a watch on x: i see the name Text Box 2, as expected. I see
that gs.Count is 2 as expected.
When I step into the function and set a watch on s, I see that its Name is
Text Box 2, as expected.
I single step and when the line xx = s.TextFrame.Characters.Text is
executed, i get runtime error 13 type mismatch.

Here is my exact code. I copied from here back to the code window and ran it
to make absolutely sure.

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

Sub FindTB(s As Shape)
Dim i As Integer
Dim x As Shape
Dim xx As String
xx = "never found"
Dim gs As GroupShapes
If s.Type = msoTextBox Then
xx = s.TextFrame.Characters.Text
MsgBox (s.Name & xx)
ElseIf s.Type = msoGroup Then
MsgBox (s.Name)
Set gs = s.GroupItems
For i = 1 To gs.Count
Set x = gs.Item(i)
FindTB x
Next
End If
End Sub
 
J

Jim Cone

You have to ungroup shapes before you can get at the individual shape properties.
See code below.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
Sub SearchAllTBs_R1()
'Visit all sheets in all open workbooks.
'Calls FindTB_R1 if any shapes on a sheet.
Dim i As Long
Dim j As Long
Dim s As Shape
For i = 1 To Workbooks.Count
Workbooks(i).Activate
For j = 1 To Sheets.Count
For Each s In Worksheets(j).Shapes
FindTB_R1 s
Next
Next j
Next i
Set s = Nothing
End Sub
'--
Sub FindTB_R1(sh As Shape)
'Calls LookInsideGroup function.
Dim xx As String
xx = "never found"
If sh.Type = msoTextBox Then
xx = sh.TextFrame.Characters.Text
MsgBox sh.Name & " " & xx
ElseIf sh.Type = msoGroup Then
MsgBox sh.Name
Call LookInsideGroup(sh)
End If
End Sub
'--
Function LookInsideGroup(ByRef shp As Shape)
'Jim Cone - Portland Oregon - May 2008
Dim shpRng As ShapeRange
Dim arrShps() As Variant
Dim sTitle As String
Dim c As Long
Dim N As Long

c = shp.GroupItems.Count
ReDim arrShps(1 To c)
For N = 1 To c
arrShps(N) = shp.GroupItems(N).Name
Next
sTitle = shp.Name
shp.Ungroup
Set shpRng = shp.Parent.Shapes.Range(arrShps)
For N = 1 To c
If shpRng(N).Type = msoTextBox Then
MsgBox shpRng(N).Name & " " & _
shpRng(N).TextFrame.Characters.Text
End If
Next
shpRng.Regroup
shpRng.Name = sTitle
Set shpRng = Nothing
End Function
'--




"LurkingMan"
wrote in message
I took your advice about option explicit and also minimized my test case as
you described. I tried my code on multiple machines and got the same error,
so you are right about it not being a versioning issue.
Still, no progress. Since I'm new to this i might be assuming something, so
I'm going to describe my steps in exhausting detail.

Here are my exact steps:
I create a new workbook and since it has 3 sheets I delete sheet2 and sheet3.
I use the drawing toolbar to add a textbox.
I copy it twice and add box1 box2 and box3 as text.
I group box 2 and 3by selecting them with shift-click and then
right-clicking and choosing group from the menu.
I hit select all and verify that's all that's on the worksheet.
I hit alt f11 and right-click on Microsoft excel objects in the vba project
pane and select "insert module"

I paste the code shown below into the code window and run.
I get a Message box with Text Box1box1
I click OK and get a message box with Text Box2box2
I click OK and get a message box with Group 4
I click OK and get run time error 13 type mismatch
When i click debug the line
xx = s.TextFrame.Characters.Text
is pointed at.

When i look at the call stack I see
VBAProject.Module1.FindTB
VBAProject.Module1.FindTB
VBAProject.Module1.SearchAllTBs

When I reexecute, I set a break point at the call toFindTB x that's inside
FindTB and add a watch on x: i see the name Text Box 2, as expected. I see
that gs.Count is 2 as expected.
When I step into the function and set a watch on s, I see that its Name is
Text Box 2, as expected.
I single step and when the line xx = s.TextFrame.Characters.Text is
executed, i get runtime error 13 type mismatch.

Here is my exact code. I copied from here back to the code window and ran it
to make absolutely sure.
-SNIP-
In theory, there is no difference between theory and practice.
In practice, there is no similarity.
 
L

LurkingMan

Thanks Jim. Ungrouping and regrouping lets it all work.

I saw this in the VB help and was led to believe I could work with objects
without ungrouping them.
GroupShapes Collection Object
Represents the individual shapes within a grouped shape. Each shape is
represented by a Shape object. Using the Item method with this object, you
can work with single shapes within a group without having to ungroup them.

Hmm, there's also the point that I could get .Name from the shape without
ungrouping. Do you happen to know why one member function works and another
doesn't?
 
J

Jim Cone

Sometimes the help file is wrong and sometimes things just don't work.
I just remember (quite awhile back) going thru a lot of trials with group
shapes before I found something that worked.
I then saved the code for future reference. Also, I recall that the array
used to create the ShapeRange must be a variant data type or things fail.
Jim Cone
Portland, Oregon


"LurkingMan"
<[email protected]>
wrote in message
Thanks Jim. Ungrouping and regrouping lets it all work.

I saw this in the VB help and was led to believe I could work with objects
without ungrouping them.
GroupShapes Collection Object
Represents the individual shapes within a grouped shape. Each shape is
represented by a Shape object. Using the Item method with this object, you
can work with single shapes within a group without having to ungroup them.

Hmm, there's also the point that I could get .Name from the shape without
ungrouping. Do you happen to know why one member function works and another
doesn't?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

finding text in all textboxes 4
VBA - exporting serial letters to single documents 0
Remove Identical words 0
Delete sheets code error requires object 2
VBA - (re-) Set a Table Style 2
Mass-remove hyperlinks from Publisher Document (VBA macro) 0
Permutations andText to column problem 6
Changing the test of checkboxes (shapes) programmatically? 4

Top