Tags: activechart, boxes, couple, excel, group, grouped, grouping, label, microsoft, msdn, rectangle, select, shapes, software, text, together, vba
VBA Grouping Rectangle/Text Boxes
On Microsoft » Microsoft Excel
9,751 words with 5 Comments; publish: Wed, 04 Jun 2008 18:10:00 GMT; (30678.13, « »)
Hey all,
I am trying to label a couple of text boxes that are grouped together within
a rectangle.
ActiveChart.Shapes("Group 29").Select
Selection.ShapeRange.Ungroup.Select
ActiveChart.PlotArea.Select
ActiveChart.Shapes("Group 28").Select
Selection.ShapeRange.Ungroup.Select
ActiveChart.PlotArea.Select
ActiveChart.Shapes("Text Box 17").Select
Selection.Characters.Text = Sheets(Idx + 1).Range("D16")
ActiveChart.Shapes("Text Box 18").Select
Selection.Characters.Text = Sheets(Idx + 1).Range("E16")
Selection.ShapeRange.Regroup.Select
ActiveChart.Shapes("Rectangle 13").Select
Selection.ShapeRange.Regroup.Select
Unfortunately, the GROUP ID changes each time I regroup so that what says
"Group 29" now becomes "Group 30"? Any work around for this?
Lance
http://excel.itags.org/q_microsoft-excel_470927.html
All Comments
Leave a comment...
- 5 Comments
- According to VBA help, there is a way to edit the elements within a group
without ungrouping. You can access the shape using GroupItems. For example,
I could change the fill color of a shape within a groupp using:
activechart.Shapes("Group 7").GroupItems(1).fill.ForeColor.RGB =rgb(255,0,0)
Unfortunately you cannot use syntax like
Shapes("Group 7").GroupItems("Rectangle 4")
to refer to the grouped shape by name. Also, I could not get this to help me
change the text of a grouped textbox.
What I was going to suggest before I tried the above was, name the objects
as you draw them. Combine this with not selecting everything before editing
it, and you get compact code like this:
Sub CreateGroup()
With ActiveChart.Shapes
.AddShape(msoShapeRectangle, 50, 50, 50, 25).Name = "BigRect"
.AddShape(msoShapeOval, 70, 60, 10, 10).Name = "SmallCircle"
With .AddTextbox(msoTextOrientationHorizontal, 60, 25, _
75, 20)
.TextFrame.Characters.Text = "abcde"
.Name = "MyText"
End With
.Range(Array("MyText", "BigRect", "SmallCircle")).Group.Name ="Group_One"
End With
End Sub
Sub AdjustGroup()
With ActiveChart
.Shapes("Group_One").Ungroup
.Shapes("MyText").TextFrame.Characters.Text = "Hello!"
.Shapes.Range(Array("MyText", "BigRect", "SmallCircle")).Group.Name ="Group_One"
End With
End Sub
- Jon
--
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Lance Hoffmeyer" <lance.excel.itags.org.augustmail.com> wrote in message
news:pYtCj.11753$wM2.8838.excel.itags.org.trnddc07...
> Hey all,
> I am trying to label a couple of text boxes that are grouped together
> within
> a rectangle.
>
> ActiveChart.Shapes("Group 29").Select
> Selection.ShapeRange.Ungroup.Select
> ActiveChart.PlotArea.Select
> ActiveChart.Shapes("Group 28").Select
> Selection.ShapeRange.Ungroup.Select
> ActiveChart.PlotArea.Select
> ActiveChart.Shapes("Text Box 17").Select
> Selection.Characters.Text = Sheets(Idx + 1).Range("D16")
> ActiveChart.Shapes("Text Box 18").Select
> Selection.Characters.Text = Sheets(Idx + 1).Range("E16")
> Selection.ShapeRange.Regroup.Select
> ActiveChart.Shapes("Rectangle 13").Select
> Selection.ShapeRange.Regroup.Select
>
> Unfortunately, the GROUP ID changes each time I regroup so that what says
> "Group 29" now becomes "Group 30"? Any work around for this?
> Lance
#1; Wed, 04 Jun 2008 18:11:00 GMT
- > Unfortunately you cannot use syntax like
> Shapes("Group 7").GroupItems("Rectangle 4")
you can do this -
For Each shp in activesheet/chart.Shapes("Group 7").GroupItems
if shp.name = "Rectangle 4" then
etc, eg
shp.fill.forecolor.schemecolor = 6 + 7
Unfortunately, like you, I have never found a way of changing text in a
grouped shape without first ungrouping, font formats neither.
Don't suppose following will be of slightest interest to anyone, but FWIW,
writing (and to a lesser extent reading)individual grouped shape properties
can fail from a dll used as a Com-addin. Yet the exact same dll does it all
fine if used as an ordinary dll called from VBA.
Regards,
Peter T
"Jon Peltier" <jonxlmvpNO.excel.itags.org.SPAMpeltiertech.com> wrote in message
news:OFE7LNdhIHA.5900.excel.itags.org.TK2MSFTNGP02.phx.gbl...
> According to VBA help, there is a way to edit the elements within a group
> without ungrouping. You can access the shape using GroupItems. For
example,
> I could change the fill color of a shape within a groupp using:
> activechart.Shapes("Group 7").GroupItems(1).fill.ForeColor.RGB => rgb(255,0,0)
> Unfortunately you cannot use syntax like
> Shapes("Group 7").GroupItems("Rectangle 4")
> to refer to the grouped shape by name. Also, I could not get this to help
me
> change the text of a grouped textbox.
> What I was going to suggest before I tried the above was, name the objects
> as you draw them. Combine this with not selecting everything before
editing
> it, and you get compact code like this:
> Sub CreateGroup()
> With ActiveChart.Shapes
> .AddShape(msoShapeRectangle, 50, 50, 50, 25).Name = "BigRect"
> .AddShape(msoShapeOval, 70, 60, 10, 10).Name = "SmallCircle"
> With .AddTextbox(msoTextOrientationHorizontal, 60, 25, _
> 75, 20)
> .TextFrame.Characters.Text = "abcde"
> .Name = "MyText"
> End With
> .Range(Array("MyText", "BigRect", "SmallCircle")).Group.Name => "Group_One"
> End With
> End Sub
> Sub AdjustGroup()
> With ActiveChart
> .Shapes("Group_One").Ungroup
> .Shapes("MyText").TextFrame.Characters.Text = "Hello!"
> .Shapes.Range(Array("MyText", "BigRect", "SmallCircle")).Group.Name => "Group_One"
> End With
> End Sub
> - Jon
> --
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
> "Lance Hoffmeyer" <lance.excel.itags.org.augustmail.com> wrote in message
> news:pYtCj.11753$wM2.8838.excel.itags.org.trnddc07...
> > Hey all,
> >
> > I am trying to label a couple of text boxes that are grouped together
> > within
> > a rectangle.
> >
> >
> > ActiveChart.Shapes("Group 29").Select
> > Selection.ShapeRange.Ungroup.Select
> > ActiveChart.PlotArea.Select
> > ActiveChart.Shapes("Group 28").Select
> > Selection.ShapeRange.Ungroup.Select
> > ActiveChart.PlotArea.Select
> > ActiveChart.Shapes("Text Box 17").Select
> > Selection.Characters.Text = Sheets(Idx + 1).Range("D16")
> > ActiveChart.Shapes("Text Box 18").Select
> > Selection.Characters.Text = Sheets(Idx + 1).Range("E16")
> > Selection.ShapeRange.Regroup.Select
> > ActiveChart.Shapes("Rectangle 13").Select
> > Selection.ShapeRange.Regroup.Select
> >
> >
> > Unfortunately, the GROUP ID changes each time I regroup so that what
says
> > "Group 29" now becomes "Group 30"? Any work around for this?
> >
> > Lance
>
#2; Wed, 04 Jun 2008 18:12:00 GMT
- >> Unfortunately you cannot use syntax like
>> Shapes("Group 7").GroupItems("Rectangle 4")
> you can do this -
> For Each shp in activesheet/chart.Shapes("Group 7").GroupItems
> if shp.name = "Rectangle 4" then
> etc, eg
> shp.fill.forecolor.schemecolor = 6 + 7
I've gotten into the habit of looping with a counter:
For iItem = 1 to blah.Shapes("Group 7").GroupItems.Count
Set shp = blah.Shapes("Group 7").GroupItems(iItems)
because sometimes it seems that For Each misses one or two items.
> Unfortunately, like you, I have never found a way of changing text in a
> grouped shape without first ungrouping, font formats neither.
Glad it's not just me.
- Jon
--
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
#3; Wed, 04 Jun 2008 18:13:00 GMT
- > I've gotten into the habit of looping with a counter:
> For iItem = 1 to blah.Shapes("Group 7").GroupItems.Count
> Set shp = blah.Shapes("Group 7").GroupItems(iItems)
> because sometimes it seems that For Each misses one or two items.
Indeed it can (miss items) or worse error (eg for each series in certain
chart types). I vaguely recall we discussed this before a while ago.
Regards,
Peter T
#4; Wed, 04 Jun 2008 18:14:00 GMT
- I recall several discussions with different people. The specifics are vague,
but the nagging feeling that I shouldn't do it that way are pretty strong.
- Jon
--
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Peter T" <peter_t.excel.itags.org.discussions> wrote in message
news:%23Hvm%23GfhIHA.4076.excel.itags.org.TK2MSFTNGP05.phx.gbl...
>> I've gotten into the habit of looping with a counter:
>> For iItem = 1 to blah.Shapes("Group 7").GroupItems.Count
>> Set shp = blah.Shapes("Group 7").GroupItems(iItems)
>> because sometimes it seems that For Each misses one or two items.
> Indeed it can (miss items) or worse error (eg for each series in certain
> chart types). I vaguely recall we discussed this before a while ago.
> Regards,
> Peter T
>
#5; Wed, 04 Jun 2008 18:15:00 GMT