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


"Greg Wilson" wrote:

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


"Jeff" wrote:

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?