Chart Object

Multiple objects Chart
Multiple objects

Represents a chart in a workbook. The chart can be either an embedded chart (contained in a ChartObject) or a separate chart sheet.

Using the Chart Object

The following properties and methods for returning a Chart object are described in this section:

• Chart property
• Charts method
• ActiveChart property
• ActiveSheet property

Chart Property

Use the Chart property to return a Chart object that represents the chart contained in a ChartObject object. The following example sets the pattern for the chart area in embedded chart one on the worksheet named "Sheet1."

Worksheets("Sheet1").ChartObjects(1).Chart. _
    ChartArea.Interior.Pattern = xlLightDown

Charts Method

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 following example changes the color of series one on chart sheet one.

Charts(1).SeriesCollection(1).Interior.Color = RGB(255, 0, 0)

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’re 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 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. The Sheets collection 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.

ActiveChart Property

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 it’s been activated with the Activate method. The following example activates chart sheet one 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 that it’s contained in has been activated with the Activate method. The following example activates embedded chart one on worksheet one 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.Type = xlLine
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "January Sales"

ActiveSheet Property

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 one in the chart to blue.

Charts("chart1").Activate
ActiveSheet.SeriesCollection(1).Interior.ColorIndex = 5