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

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.todaysummary.com.augustmail.com> wrote in message

      news:pYtCj.11753$wM2.8838.excel.todaysummary.com.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.todaysummary.com.SPAMpeltiertech.com> wrote in message

      news:OFE7LNdhIHA.5900.excel.todaysummary.com.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.todaysummary.com.augustmail.com> wrote in message

      > news:pYtCj.11753$wM2.8838.excel.todaysummary.com.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.todaysummary.com.discussions> wrote in message

      news:%23Hvm%23GfhIHA.4076.excel.todaysummary.com.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