Chart Object (Excel)
Represents a chart in a workbook.
Remarks
The chart can be either an embedded chart (contained in a ChartObject object) or a separate chart sheet.
The following properties and methods for returning a Chart object are described in the example section:
Charts method
ActiveChart property
ActiveSheet property
Example
The Charts collection contains a Chart object for each chart sheet in a workbook. Use Charts(index), where index is the chart-sheet index number or name, to return a single Chart object. The chart index number represents the position of the chart sheet on the workbook tab bar. Charts(1) is the first (leftmost) chart in the workbook; Charts(Charts.Count) is the last (rightmost). All chart sheets are included in the index count, even if they are hidden. The chart-sheet name is shown on the workbook tab for the chart. You can use the Name property to set or return the chart name. The following example changes the color of series 1 on chart sheet 1.
Charts(1).SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbRed
The following example moves the chart named Sales to the end of the active workbook.
Charts("Sales").Move after:=Sheets(Sheets.Count)
The Chart object is also a member of the Sheets collection, which contains all the sheets in the workbook (both chart sheets and worksheets). Use Sheets(index), where index is the sheet index number or name, to return a single sheet.
When a chart is the active object, you can use the ActiveChart property to refer to it. A chart sheet is active if the user has selected it or if it has been activated with the Activate method of the Chart object or the Activate method of the ChartObject object. The following example activates chart sheet 1 and then sets the chart type and title.
Charts(1).Activate
With ActiveChart
.Type = xlLine
.HasTitle = True
.ChartTitle.Text = "January Sales"
End With
An embedded chart is active if the user has selected it or the ChartObject object in which it is contained has been activated with the Activate method. The following example activates embedded chart 1 on worksheet 1 and then sets the chart type and title. Notice that after the embedded chart has been activated, the code in this example is the same as that in the previous example. Using the ActiveChart property allows you to write Visual Basic code that can refer to either an embedded chart or a chart sheet (whichever is active).
Worksheets(1).ChartObjects(1).Activate
ActiveChart.ChartType = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "January Sales"
When a chart sheet is the active sheet, you can use the ActiveSheet property to refer to it. The following example uses the Activate method to activate the chart sheet named Chart1 and then sets the interior color for series 1 in the chart to blue.
Charts("chart1").Activate
ActiveSheet.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue