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

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

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

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

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

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

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