Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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? -- In theory, there is no difference between theory and practice. In practice, there is no similarity. "Jim Cone" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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" 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? -- In theory, there is no difference between theory and practice. In practice, there is no similarity. "Jim Cone" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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? -- In theory, there is no difference between theory and practice. In practice, there is no similarity. "Jim Cone" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
why does s.TextFrame.Characters.Text work only sometimes?
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" 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? -- In theory, there is no difference between theory and practice. In practice, there is no similarity. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Center text in a shape textframe | Excel Programming | |||
use VBA to tell if all the text in a TextBox or TextFrame can be displayed | Excel Programming | |||
TextFrame.Characters.Text returns truncated string | Excel Programming | |||
cell to textframe using characters object | Excel Programming | |||
.TextFrame.Characters.Text property readOnly in function?? | Excel Programming |