How to: Add Chart Controls to Worksheets

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Document-level projects Excel 2007 Excel 2003 Application-level projects Excel 2007 For more information, see Features Available by Application and Project Type.

You can add Chart controls to a Microsoft Office Excel worksheet at design time and at run time in document-level customizations.

Starting in Visual Studio 2008 Service Pack 1 (SP1), you can add Chart controls at run time in application-level add-ins.

This topic describes the following tasks:

• Adding Chart controls at design time

• Adding Chart controls at run time in a document-level project

• Adding Chart controls at run time in an application-level project

For more information about Chart controls, see Chart Control.

Adding Chart Controls at Design Time

You can add the Chart control to your worksheet in the same manner you would add a chart from within the application.

Note

The Chart control is not available from the Toolbox or the Data Sources window.

To add a Chart host control to a worksheet using the Chart Wizard in Excel 2003

1. On the Insert menu, click Chart.

   The Chart Wizard appears.

   Note

   If the Insert menu is not available on the toolbar, click the document to give it focus.

2. Select the type of chart you want in the Standard Types or Custom Types tab of the Chart Wizard and click Next.

3. Select the range of cells that contains the data for the chart (cells A5 through D8) and click Next.

Select the desired options in Step 3 of 4 of the Chart Wizard and click Finish.

To add a Chart host control to a worksheet in Excel 2007

1. On the Insert tab, in the Charts group, click Column, click a category of charts, and then click the type of chart you want.

2. In the Insert Chart dialog box, click OK.

3. On the Design tab, in the Data group, click Select Data.

4. In the Select Data Source dialog box, click in the Chartdata range box and clear any default selection.

5. In the Data for Chart sheet, select the range of cells that contains the data for the chart (cells A5 through D8).

6. In the Select Data Source dialog box, click OK.

Adding Chart Controls at Run Time in a Document-Level Project

You can add the Chart control dynamically at run time. Dynamically created charts are not persisted in the document as host controls when the document is closed. For more information, see Adding Controls to Office Documents at Run Time.

To add a Chart control to a worksheet programmatically

• In the Startup event handler of Sheet1, insert the following code to add the Chart control.

  Dim employeeData As Microsoft.Office.Tools.Excel.Chart
  employeeData = Me.Controls.AddChart(25, 110, 200, 150, "employees")
  employeeData.ChartType = Excel.XlChartType.xl3DPie
  
  ' Gets the cells that define the data to be charted. 
  Dim chartRange As Excel.Range = Me.Range("A5", "D8")
  employeeData.SetSourceData(chartRange)
  

  Microsoft.Office.Tools.Excel.Chart employeeData;
  employeeData = this.Controls.AddChart(25, 110, 200, 150, "employees");
  employeeData.ChartType = Excel.XlChartType.xl3DPie;
  
  // Gets the cells that define the data to be charted.
  Excel.Range chartRange = this.get_Range("A5", "D8");
  employeeData.SetSourceData(chartRange, missing);
  

Adding Chart Controls at Run Time in an Application-Level Project

Starting in SP1, you can add a Chart control programmatically to any open worksheet in an application-level add-in project. For more information, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

Dynamically created chart controls are not persisted in the worksheet as host controls when the worksheet is closed. For more information, see Adding Controls to Office Documents at Run Time.

To add a Chart control to a worksheet programmatically

• The following code generates a worksheet host item that is based on the open worksheet, and then adds a Chart control.

  Private Sub AddChart()
      Dim worksheet As Worksheet = CType(Application.ActiveWorkbook.ActiveSheet,  _
          Excel.Worksheet).GetVstoObject()
      Dim cells As Excel.Range = worksheet.Range("A5", "D8")
      Dim chart As Chart = worksheet.Controls.AddChart(cells, "employees")
      chart.ChartType = Excel.XlChartType.xl3DPie
      chart.SetSourceData(cells, Type.Missing)
  
  End Sub
  

  private void AddChart()
  {
      Worksheet worksheet =
          ((Excel.Worksheet)Application.
                ActiveWorkbook.ActiveSheet).GetVstoObject();
      Excel.Range cells = worksheet.Range["A5", "D8"];
      Chart chart = worksheet.Controls.AddChart(cells, "employees");
      chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie;
      chart.SetSourceData(cells, missing);
  }       
  

Compiling the Code

This example has the following requirements:

• Data to be charted, stored in the range from A5 to D8 in the worksheet.

See Also

Concepts

Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time

Excel Application-Level Add-in Development

Chart Control

Host Items and Host Controls Overview

Binding Data to Controls in Office Solutions

ChartSheet Host Item

Programmatic Limitations of Host Items and Host Controls

Other Resources

Controls on Office Documents

Excel Host Controls

Change History

Date	History	Reason
July 2008	Added a section that shows how to add a chart control to a worksheet in an application-level add-in.	SP1 feature change.