View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
LurkingMan LurkingMan is offline
external usenet poster
 
Posts: 4
Default why does s.TextFrame.Characters.Text work only sometimes?

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


--
In theory, there is no difference between theory and practice.
In practice, there is no similarity.