ExcelBanter - VBA Grouping Rectangle/Text Boxes

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA Grouping Rectangle/Text Boxes (https://www.excelbanter.com/excel-programming/407686-vba-grouping-rectangle-text-boxes.html)

VBA Grouping Rectangle/Text Boxes

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

VBA Grouping Rectangle/Text Boxes

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" wrote in message
news:pYtCj.11753$wM2.8838@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

VBA Grouping Rectangle/Text Boxes

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" wrote in message
...
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" wrote in message
news:pYtCj.11753$wM2.8838@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

VBA Grouping Rectangle/Text Boxes

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

VBA Grouping Rectangle/Text Boxes

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@discussions wrote in message
...
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