Microsoft Office Tutorials and References
In Depth Information
Changing the location of all charts
Changing the location of all charts
The Location method of a Chart object moves a chart. You can use this method to
convert embedded charts to Chart sheets (and vice versa).
The following macro loops through all embedded charts on the active sheet and
moves each chart to a Chart sheet.
Sub ConvertToChartSheets()
Dim ChtObj As ChartObject
For Each ChtObj In ActiveSheet.ChartObjects
ChtObj.Chart.Location Where:=xlLocationAsNewSheet
Next ChtObj
End Sub
The macro that follows performs the opposite task: It loops through all Chart
sheets and converts each chart to an embedded chart on the active sheet.
Sub ConvertToEmbeddedCharts()
Dim Cht As Chart
For Each Cht In ActiveWorkbook.Charts
Cht.Location Where:=xlLocationAsObject, Name:=ActiveSheet.Name
Next Cht
End Sub
After you run the ConvertToEmbeddedCharts macro, all the embedded
charts are stacked on top of each other. You may want to modify this macro
such that the embedded charts are better positioned. For more information,
see “Sizing and Aligning Charts,” later in this chapter.
Deleting Charts
To delete an embedded chart, you must delete its ChartObject container. The
following statement, for example, deletes the chart named Chart 1 on the active sheet:
ActiveSheet.ChartObjects(“Chart 1”).Delete
To delete all embedded charts on the active sheet, use the Delete method of the
ChartObjects collection:
Worksheets(“Sheet1”).ChartObjects.Delete
Search JabSto ::




Custom Search