Trouble selecting chart in macro

[Jeff]

I created a macro to ungroup a chart and autoshape object to edit the
autoshape. I then created a macro to regroup the chart. However, when I run
the macro, I receive an error message that "The item with the specified name
wasn't found." I tried to define a name for the object but each time the
group of objects is edited the name of the group changes (e.g., group 12,
group 13, etc.). How can I refer to this one chart/grouping in the macro?

 

[Greg Wilson]

When a grouped shape object is ungrouped and then regrouped Excel gives it a
new name. There is no way I know of to know ahead of time what it will be.
Try this approach instead.

Note that it isn't necessary to ungroup to make most changes. You can return
a reference to an individual item using the "GroupItems" method and then make
changes to a specified item without ungrouping. An exception is changing
textframe text unless this has changed with new versions.

Sub RegroupTest()
Dim grpshape As Shape
Dim sr As ShapeRange
'Set grpshape to grouped shape object
Set grpshape = ActiveSheet.Shapes(1)
Set sr = grpshape.Ungroup
With sr.Item(2)
MsgBox .Name
'The following line will succeed
'.TextFrame.Characters.Text = "Test 1234"
.Fill.ForeColor.RGB = RGB(200, 100, 150)
End With
'If finished use this syntax:
sr.Regroup
'To get a reference to the regrouped shape
'to do other things use this syntax:
'Set grpshape = sr.Regroup
End Sub

Sub NoUngroupTest()
Dim grpshape As Shape
'Set grpshape to grouped shape object
Set grpshape = ActiveSheet.Shapes(1)
With grpshape
MsgBox .Name
'The following line will fail
'.TextFrame.Characters.Text = "monkey"
.GroupItems(2).Fill.ForeColor.RGB = RGB(100, 150, 150)
End With
End Sub

 

[Jeff]

Maybe it will help to show the code I have:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+j
'
ActiveSheet.Shapes("Graph").Select
Selection.ShapeRange.Ungroup.Select
ActiveSheet.Shapes("Rectangle 7").Select
Selection.ShapeRange.ZOrder msoBringToFront
End Sub
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Shapes.Range(Array("Rectangle 7", "Chart 1")).Select
Selection.ShapeRange.Group.Select
ActiveWorkbook.Names.Add Name:="Graph", RefersToR1C1:="=""Group 13"""
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Selection.Copy
End Sub

Will the technique you're suggesting work for the code I'm using?

 

[Greg Wilson]

Note that it only takes one macro to toggle the ZOrder of the rectangle
because the shape range index of 1 always refers to the item with the lowest
ZOrder. Therefore, if the rectangle is behind the chart then sr(1) refers to
it and it is brought to the front. If the chart is behind the rectangle then
sr(1) refers to it instead and the chart is brought to the front.

I confirmed that the ZOrder property, like textframe text, requires
ungrouping before it can be changed.

If you want to go on to do other things after regrouping then declare
another variable as Shape, e.g. Dim shp As Shape. Then add this at the end:

Set shp = sr.Regroup
With shp
'yada yada yada
End With

If you only want to do a single thing such as copy then:

sr.Regroup.Copy

Copying will greatly reduce performance however.

The code assumes that ActiveSheet.Shapes(1) refers to the grouped shape.
Change to the correct index number. Note that I was wrong when I said there
is no way to know ahead of time what the new name of the regrouped shape will
be. It simply increments by 1. For example, "Group 19" becomes "Group 20"
when ungrouped and regrouped. I was thinking of handles to windows.

Sub ToggleZOrder()
Dim sr As ShapeRange
With ActiveSheet.Shapes(1)
Set sr = .Ungroup
End With
sr(1).ZOrder msoBringToFront
sr.Regroup '.Copy
End Sub

Regards,
Greg

 

[Greg Wilson]

Jeff,

You might also consider not grouping the chart and rectangle but adding the
rectangle to the chart instead of to the worksheet. The rectangle will be
owned by the chart instead of the worksheet. Thus, you can avoid
grouping/ungrouping.

Manual method:
1. Display the Drawing toolbar
2. Activate the chartobject
3. Click the rectangle icon on the toolbar and then click inside the
boundaries of the chart.
4. Position and size the rectangle as desired.

Now drag the chart around. The rectangle should be attached to (owned by)
the chart. This code will do similar to toggling the rectangle's ZOrder:

With ActiveSheet.ChartObjects(1).Chart
.Shapes(1).Visible = Not .Shapes(1).Visible
End With

Adding Rectangle to Chart Programmaticly:

Sub AddRectToChart()
Dim cht As Chart
Set cht = ActiveSheet.ChartObjects(1).Chart
cht.Shapes.AddShape 1, 100, 100, 100, 100
End Sub

Regards,
Greg

 

[Jeff]

Greg,
Your code has helped tremendously. I guess I'm wondering now, how I can
modify the the second macro to place the object behind the chart and regroup
the items where I had the same trouble as the first macro. Is it possible to
select the rectangle object and place it behind the chart, then regroup. I'd
prefer not to incorporate the rectangle within the chart object. Any more
advice you can offer would be appreciated.

Sub Macro2()
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Shapes.Range(Array("Rectangle 7", "Chart 1")).Select
Selection.ShapeRange.Group.Select
ActiveWorkbook.Names.Add Name:="Graph", RefersToR1C1:="=""Group 13"""
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Selection.Copy
End Sub

 

[Jeff]

Greg,
Please ignore this post I made. I see the code you gave me toggles the
position and will do the trick (hence the name "toggle"). Thanks for your
help!!!
Jeff