Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 _______ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign a text to a rectangle | Excel Discussion (Misc queries) | |||
Text Box vs. rectangle, what is the difference? | Excel Discussion (Misc queries) | |||
text does not update in rectangle box | Excel Programming | |||
Change text in rectangle shape | Excel Programming | |||
Add text to a rectangle in VBA | Excel Programming |