Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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 "Jeff" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble selecting chart in macro
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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pie Chart trouble for 2007 | Charts and Charting in Excel | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
trouble selecting a named cell | Excel Programming | |||
chart trouble | Charts and Charting in Excel | |||
selecting a picture within a chart via a macro | Excel Programming |