Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Hi,
My macro fails when I try to have text within a textbox that is grouped with a trapezoid (Autoshape). The text box is contained with the trapezoid. What's driving me crazy is that I can update the text box manually when they are grouped, but the macro fails when it tries to do the same thing. Is there anyway to update the text box without ungrouping and then regrouping? I want to avoid this because the group number incremements every time you re-group and I remember reading that when an object number gets too large, the macro will fail. Below is my code -- thanks in advance for your help!! Sub updatewhengrouped() ActiveSheet.Shapes("Text Box 7").Select Selection.Characters.Text = "Always thought..." Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Do not use selection more then nescesary!(Macro recorder sins should
not be repeated) In this case the selection contains different types of shapes, and this makes that properties that are not available for all items in the selection cannot be used. for a Shapecontrol (i.e. a control from the Forms toolbar) use ActiveSheet.Shapes("Text Box 7").ControlFormat.Value ="myvalue" DM Unseen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
AFAIK it's not possible to change the text string in a grouped item without
ungrouping, manually or programmatically. You say you can manually - how, or maybe I've misunderstood. You can though change font properties to the entire group. You're right about the group number identifier incrementing each time ungrouped & regrouped. You can give it your own name and reapply when regrouped. But it doesn't stop the object id number increment, which will apply to this when regrouped, or any subsequently added shapes. It's annoying, intuitively one might think problems could arise if the number gets too large but that's never occurred for me, even with the number getting into many 10,000's. Regards, Peter T "Linking to specific cells in pivot table" crosoft.com wrote in message ... Hi, My macro fails when I try to have text within a textbox that is grouped with a trapezoid (Autoshape). The text box is contained with the trapezoid. What's driving me crazy is that I can update the text box manually when they are grouped, but the macro fails when it tries to do the same thing. Is there anyway to update the text box without ungrouping and then regrouping? I want to avoid this because the group number incremements every time you re-group and I remember reading that when an object number gets too large, the macro will fail. Below is my code -- thanks in advance for your help!! Sub updatewhengrouped() ActiveSheet.Shapes("Text Box 7").Select Selection.Characters.Text = "Always thought..." Range("A1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
to get to an shape of a group use:
say you have a "group 4"(or whatever you call it), and that contains a "textbox 3" which needs anew value of 333. Activesheet.Shapes("Group 4").GroupItems("TextBox 3").ControlFormat.Value = 333 DM Unseen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
We might be at cross purposes here.
Most properties of a grouped item can be changed as per your example for a grouped control. However, referring to the OP's question, if you know how to change the text in a grouped item (textframe) without ungrouping I'd also be pleased to know. Regards, Peter T "DM Unseen" wrote in message oups.com... to get to an shape of a group use: say you have a "group 4"(or whatever you call it), and that contains a "textbox 3" which needs anew value of 333. Activesheet.Shapes("Group 4").GroupItems("TextBox 3").ControlFormat.Value = 333 DM Unseen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Peter,
My code does not "ungroup" it just changes part of a shape-group(albeit a *control* textbox and not a "shape" tetxbox) After your comment I checked some more on the "Drawing textbox" and can confirm that: grouping drawing shapes, including the "drawing" textbox makes the "characters" object in VBA (and indeed the text itsself) *unavailable*. Ungrouped items do not have this issue(this is what you said). Endusres can still edit the text though of the individual items This anomaly comes with the following observation: - When editing (text) in shapegroups in excel; , excel goes into "Text Edit Mode", in this mode you can change the text of the shape(s). You notice the mode by observing a shaded rectangle around the shape. When you want to change text, excel goes into this mode so you can edit this. -When selecting a single shape Excel goes automatically into Text Edit mode when you start typing, when having a shape group you *first need to select a shape within the group, and after you can start editing the text* - Edit mode is *not* available in VBA, as soon as VBA is active, the "Text Edit mode" is deactivated and blocked and cannot be activated anymore until VBA code has ended. -The VBA characters object is only giving back text when that shape can be given the focus (as if to start the "text editing mode") Workaround: Select the items within the group *before* reading out or setting the text: Example: Sub t() Dim shpTextbox As Shape Dim tfText As TextFrame Dim c As Characters Dim shpGroup As Shape Set shpGroup = ActiveSheet.Shapes(2) ' get the groupt Set shpTextbox = shpGroup.GroupItems(1) ' get the textbox Set tfText = shpTextbox.TextFrame Set c = tfText.Characters() shpTextbox.Select ' select the shape you want to get the text from, this is important, else this code will fail! Debug.Print c.Text ' we get the text End Sub Also for other types of textboxes that needs to be grouped with shapes that do not use the "shape" textbox from the drawing toolbar, but the textbox from the "Forms" toolbar or the "Control Toolbox" you can safely group these with your shapes. These items can still be edited when grouped(see my previous post). Also, you can make them mimic a drawing textbox quite closely, so endusres will not spot the difference, and you also can do more with them. YMMV DM Unseen Hope this clarifies something. Dm Unseen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Hi DM,
I think we are still talking at cross purposes <g The OP's objective is to write text in a grouped shape that has a text frame. It's easy to return the text in such an item, in your example no need to select anything vs what your commented notes suggest "...or the code will fail". However - how to write text without ungrouping. Your example does not do that and I don't think possible. Further, both you and the OP suggest it's possible to manually edit text in a grouped item without ungrouping. I guess I must be missing something because I don't see any way to do that. Regards, Peter T PS, I was aware that your original example with controlformat did not ungroup, and also that it's possible to change just about anything in a grouped object except text, though individual font proeprties are also a problem. "DM Unseen" wrote in message oups.com... Peter, My code does not "ungroup" it just changes part of a shape-group(albeit a *control* textbox and not a "shape" tetxbox) After your comment I checked some more on the "Drawing textbox" and can confirm that: grouping drawing shapes, including the "drawing" textbox makes the "characters" object in VBA (and indeed the text itsself) *unavailable*. Ungrouped items do not have this issue(this is what you said). Endusres can still edit the text though of the individual items This anomaly comes with the following observation: - When editing (text) in shapegroups in excel; , excel goes into "Text Edit Mode", in this mode you can change the text of the shape(s). You notice the mode by observing a shaded rectangle around the shape. When you want to change text, excel goes into this mode so you can edit this. -When selecting a single shape Excel goes automatically into Text Edit mode when you start typing, when having a shape group you *first need to select a shape within the group, and after you can start editing the text* - Edit mode is *not* available in VBA, as soon as VBA is active, the "Text Edit mode" is deactivated and blocked and cannot be activated anymore until VBA code has ended. -The VBA characters object is only giving back text when that shape can be given the focus (as if to start the "text editing mode") Workaround: Select the items within the group *before* reading out or setting the text: Example: Sub t() Dim shpTextbox As Shape Dim tfText As TextFrame Dim c As Characters Dim shpGroup As Shape Set shpGroup = ActiveSheet.Shapes(2) ' get the groupt Set shpTextbox = shpGroup.GroupItems(1) ' get the textbox Set tfText = shpTextbox.TextFrame Set c = tfText.Characters() shpTextbox.Select ' select the shape you want to get the text from, this is important, else this code will fail! Debug.Print c.Text ' we get the text End Sub Also for other types of textboxes that needs to be grouped with shapes that do not use the "shape" textbox from the drawing toolbar, but the textbox from the "Forms" toolbar or the "Control Toolbox" you can safely group these with your shapes. These items can still be edited when grouped(see my previous post). Also, you can make them mimic a drawing textbox quite closely, so endusres will not spot the difference, and you also can do more with them. YMMV DM Unseen Hope this clarifies something. Dm Unseen |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Peter,
I showed that you need to select the item in the group to actually get the text (and you can also clear the text as well). but setting it errors out (grrrrrrrrrrrrrrr) whatever I try. So i guess you're right in the sense that Excel VBA does not allow you to set individual texts of shapes directly through VBA:( There is a workaround however(a quite nice one as well). Each shape that can contain text also has a formula property so you could work around this with linking to a cell and then removing the link:) Sub t() Dim shpTextbox As Shape Dim shpGroup As Shape Set shpGroup = ActiveSheet.Shapes(2) Set shpTextbox = shpGroup.GroupItems(2) shpTextbox.Select Range("A1") = "mytext" Selection.Formula = "=A1" Selection.Formula = vbNullString Debug.Print Selection.Characters.Text Debug.Print Selection.Formula End Sub This is getting fun! You could of course leave the formula, that would be way better, but it works!!!!! DM Unseen |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Hi DM,
For me it's not necessary to select a grouped item to return the text, and I can't select a grouped item even if I want to. On which point this line in your example shpTextbox.Select errors for me with permission denied If you are able select a grouped item the only thing I can think of is there must be some version difference, I'm using XL2K. Don't have later version to hand but from memory don't think it worked in any version. I was optimistic for a moment you had come up with something with the Formula property, I had never thought of that. Sadly I cannot get that to work despite trying various other ways. So we are working with similar objects can you incorporate your formula approach or select a grouped item in the following, say in the loop after the Msgbox. Sub test() Dim v(1 To 4), i Dim shp As Shape Dim gi As Object Dim ws As Worksheet Set ws = ActiveSheet ws.DrawingObjects.Delete 'first make 4 texboxes & group For i = 1 To 4 Set shp = ws.Shapes.AddTextbox(1, 20, i * 20, 50, 15) shp.TextFrame.Characters.Text = Chr(i + 64) v(i) = shp.Name Next Set shp = ws.Shapes.Range(v).Group Application.ScreenUpdating = True i = 0 For Each gi In shp.GroupItems i = i + 1 MsgBox gi.TextFrame.Characters.Text, , gi.Name 'gi.TextFrame.Characters.Text = i & " text" '#1004 unable to set the text property 'gi.Select '#70 Permission denied Next End Sub It's also possible to get to the text in an object that supports it at the DrawingObject level, even in a grouped object, simply s = ob.Text or ob.Text = s FWIW I find this level significantly faster to read/write than shape.textframe. However still not possible to write if grouped. I'm glad you're perservering with this, I gave up a long time ago! Regards, Peter T "DM Unseen" wrote in message oups.com... Peter, I showed that you need to select the item in the group to actually get the text (and you can also clear the text as well). but setting it errors out (grrrrrrrrrrrrrrr) whatever I try. So i guess you're right in the sense that Excel VBA does not allow you to set individual texts of shapes directly through VBA:( There is a workaround however(a quite nice one as well). Each shape that can contain text also has a formula property so you could work around this with linking to a cell and then removing the link:) Sub t() Dim shpTextbox As Shape Dim shpGroup As Shape Set shpGroup = ActiveSheet.Shapes(2) Set shpTextbox = shpGroup.GroupItems(2) shpTextbox.Select Range("A1") = "mytext" Selection.Formula = "=A1" Selection.Formula = vbNullString Debug.Print Selection.Characters.Text Debug.Print Selection.Formula End Sub This is getting fun! You could of course leave the formula, that would be way better, but it works!!!!! DM Unseen |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Peter,
(this will be my last email, after that I'm on holiday:) I checked in XL2K (I was using XL2002!) and you are right, it cannot be done since the formula property is not updatable in a grouped items as well!(although it does not error out) the following code on XL2K failed: Sub test() Dim t As TextBox Set t = ActiveSheet.Shapes(1).GroupItems(2).DrawingObject 'no inside group selection possible in XL2K so we just get the object Range("A1") = "mytext" t.Formula = "$A$2" 't.Text = "text" 't.Characters.Text = "test" Debug.Print t.Formula 'forumal not updated :( End Sub Solution for you might be Sub test() Dim v(1 To 4), i Dim shp As Shape Dim gi As Object Dim ws As Worksheet Set ws = ActiveSheet ws.DrawingObjects.Delete 'first make 4 texboxes & group For i = 1 To 4 Set shp = ws.Shapes.AddTextbox(1, 20, i * 20, 50, 15) shp.DarwingObjects.Formula = Range("A1").offset(0,i) ' works when *not* grouped Range("A1").offset(0,i) = Chr(i + 64) v(i) = shp.Name Next Set shp = ws.Shapes.Range(v).Group Application.ScreenUpdating = True i = 0 For Each gi In shp.GroupItems i = i + 1 MsgBox gi.TextFrame.Characters.Text, , gi.Name 'gi.TextFrame.Characters.Text = i & " text" '#1004 unable to set the text property 'gi.Select '#70 Permission denied Next End Sub DM Unseen |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
Hi DM,
Sorry about this but I'm afraid your new line below does not work for me shp.DrawingObjects.Formula = Range("A1").Offset(0, i) Not to worry, we've probably exhausted this topic. As I said at the head I don't think there's a solution. Have a good holiday, Peter T "DM Unseen" wrote in message oups.com... Peter, (this will be my last email, after that I'm on holiday:) I checked in XL2K (I was using XL2002!) and you are right, it cannot be done since the formula property is not updatable in a grouped items as well!(although it does not error out) the following code on XL2K failed: Sub test() Dim t As TextBox Set t = ActiveSheet.Shapes(1).GroupItems(2).DrawingObject 'no inside group selection possible in XL2K so we just get the object Range("A1") = "mytext" t.Formula = "$A$2" 't.Text = "text" 't.Characters.Text = "test" Debug.Print t.Formula 'forumal not updated :( End Sub Solution for you might be Sub test() Dim v(1 To 4), i Dim shp As Shape Dim gi As Object Dim ws As Worksheet Set ws = ActiveSheet ws.DrawingObjects.Delete 'first make 4 texboxes & group For i = 1 To 4 Set shp = ws.Shapes.AddTextbox(1, 20, i * 20, 50, 15) shp.DarwingObjects.Formula = Range("A1").offset(0,i) ' works when *not* grouped Range("A1").offset(0,i) = Chr(i + 64) v(i) = shp.Name Next Set shp = ws.Shapes.Range(v).Group Application.ScreenUpdating = True i = 0 For Each gi In shp.GroupItems i = i + 1 MsgBox gi.TextFrame.Characters.Text, , gi.Name 'gi.TextFrame.Characters.Text = i & " text" '#1004 unable to set the text property 'gi.Select '#70 Permission denied Next End Sub DM Unseen |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Editing text in text box grouped within a trapezoid
I'm back!
I realised a typo slipped in (but weas not able to correct it). It should be: shp.DrawingObjects.Formula = Range("A1").Offset(0, i).Address The fotmula property only accepts an cell/range address. Hereby finishing this discussion properly;) DM Unseen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing text after concatenate | Excel Worksheet Functions | |||
Editing Text | Excel Discussion (Misc queries) | |||
Changing Text in grouped shapes | Excel Programming | |||
Editing text file | Excel Programming | |||
Tab go into the editing text box not next object | Excel Programming |