Chart Object

  • Article
Excel Developer Reference

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.

Visual Basic for Applications 
  Charts(1).SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbRed

The following example moves the chart named Sales to the end of the active workbook.

Visual Basic for Applications 
  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.

Visual Basic for Applications 
  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).

Visual Basic for Applications 
  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.

Visual Basic for Applications 
  Charts("chart1").Activate
ActiveSheet.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue

See Also