Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

Note Required applications

The features in this topic are available only if you have the required applications installed.

For more information, see Features Available by Product Combination.

  • One of these development environments:

    VSTO 2005

    -or-

    Visual Studio Team System

  • Microsoft Office 2003

This walkthrough shows the basics of changing chart styles using radio buttons on a Microsoft Office Excel 2003 worksheet. To see the result as a completed sample, see Excel Controls Sample.

During this walkthrough, you will learn how to:

  • Add a user control to a worksheet.

  • Change the chart style when an option is selected.

  • Test your project.

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To complete this walkthrough, you will need:

  • Microsoft Visual Studio 2005 Tools for the Microsoft Office System.

  • Microsoft Office Excel 2003.

You can create an Excel Workbook project using an existing workbook. In this walkthrough, you will add a chart to a workbook and then use this workbook in a new Excel solution. The data source in this walkthrough is a worksheet named Data for Chart.

To add the data

  1. Open Excel 2003.

    A new workbook opens.

  2. Right-click the Sheet3 tab and then click Rename on the shortcut menu.

  3. Rename the sheet to Data for Chart.

  4. Add the following data to Data for Chart with cell A4 being the upper left corner, and E8 the lower right corner:

    Q1 Q2 Q3 Q4

    West

    500

    550

    550

    600

    East

    600

    625

    675

    700

    North

    450

    470

    490

    510

    South

    800

    750

    775

    790

Next, add the chart to the first worksheet.

To add a chart

  1. On the Insert menu, click Chart.

    The Chart Wizard opens.

  2. Click Next, leaving the defaults selected.

  3. Click in the Data Range box and clear any default selection.

  4. In the Data for Chart sheet, select the block of cells that contains the numbers, which includes A4 in the upper left corner to E8 in the lower right corner.

  5. Click Next, and then click Next again to accept all the defaults for Step 3.

  6. In Step 4, ensure that As object in: is selected.

  7. In the As object in: list, select Sheet1.

  8. Click Finish.

  9. Reposition the chart so that the upper right corner aligns with cell E2.

  10. Save your file to the C: drive and name it ExcelChart.xls.

  11. Close Excel.

In this step, you will create an Excel Workbook project using Visual Studio Tools for Office.

To create a new project

  1. Create an Excel Workbook project with the name My Excel Chart. Make sure that Copy an existing document is selected. For more information, see How to: Create Visual Studio Tools for Office Projects.

  2. Click the Browse button and browse to the workbook you created earlier in this walkthrough.

  3. Click OK.

    Visual Studio opens the new Excel workbook in the designer and adds the My Excel Chart project to Solution Explorer.

When you create a new Excel Workbook project using an existing workbook, host controls are automatically created for any named ranges, list objects, and charts that exist within the workbook. You can change the name of the Chart control using the Properties Window.

To change the name of the Chart control

  • Select the Chart control in the designer and change the following properties in the Properties Window:

    Property Value

    Name

    dataChart

    HasLegend

    false

To add a user control

  1. Select the My Excel Chart project in Solution Explorer.

  2. On the Project menu, click Add New Item.

  3. In the Add New Item dialog box, click User Control, name the control ChartOptions, and click Add.

To add Windows Form controls to the user control

  1. If the user control is not visible in the designer, double click ChartOptions in Solution Explorer.

  2. From the Common Controls tab of the Toolbox, drag a Radio Button control to the user control, and change the following properties:

    Property Value

    Name

    columnChart

    Text

    Column Chart

  3. Add a second radio button to the user control, and change the following properties:

    Property Value

    Name

    barChart

    Text

    Bar Chart

  4. Add a third radio button to the user control, and change the following properties:

    Property Value

    Name

    lineChart

    Text

    Line Chart

  5. Add a fourth radio button to the user control, and change the following properties:

    Property Value

    Name

    areaBlockChart

    Text

    Area Block Chart

Next, write the code to update the chart when a radio button is clicked.

In this section, you will create a public event on the user control, add a property to set the selection type, and create an event handler for the CheckedChanged event of each of the radio buttons.

To create an event and property on a user control

  1. In Solution Explorer, right-click the user control and select View Code.

  2. Add code to create a SelectionChanged event and the Selection property to the ChartOptions class:

    public event EventHandler SelectionChanged;
    
    private Microsoft.Office.Interop.Excel.XlChartType selectedType = 
        Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
    
    public Microsoft.Office.Interop.Excel.XlChartType Selection
    {
        get
        {
            return this.selectedType;
        }
        set
        {
            this.selectedType = value;
        }
    }
    
    

To handle the CheckedChanged event of the radio buttons

  1. Set the chart type in the CheckedChanged event handler of the areaBlockChart radio button and then raise the event.

    private void areaBlockChart_CheckedChanged(object sender, EventArgs e)
    {
        if (((RadioButton)sender).Checked)
        {
            this.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlAreaStacked;
            if (this.SelectionChanged != null)
            {
                this.SelectionChanged(this, EventArgs.Empty);
            }
        }
    }
    
    
  2. Set the chart type in the CheckedChanged event handler of the barChart radio button.

    private void barChart_CheckedChanged(object sender, EventArgs e)
    {
        if (((RadioButton)sender).Checked)
        {
            this.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
            if (this.SelectionChanged != null)
            {
                this.SelectionChanged(this, EventArgs.Empty);
            }
        }
    }
    
    
  3. Set the chart type in the CheckedChanged event handler of the columnChart radio button.

    private void columnChart_CheckedChanged(object sender, EventArgs e)
    {
        if (((RadioButton)sender).Checked)
        {
            this.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
            if (this.SelectionChanged != null)
            {
                this.SelectionChanged(this, EventArgs.Empty);
            }
        }
    }
    
    
  4. Set the chart type in the CheckedChanged event handler of the lineChart radio button.

    private void lineChart_CheckedChanged(object sender, EventArgs e)
    {
        if (((RadioButton)sender).Checked)
        {
            this.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers;
            if (this.SelectionChanged != null)
            {
                this.SelectionChanged(this, EventArgs.Empty);
            }
        }
    }
    
    
  5. In C#, you must add event handlers for the radio buttons. You can add the code to the ChartOptions constructor, beneath the call to InitializeComponent, as shown below. For information on creating event handlers, see How to: Create Event Handlers in Visual Studio Tools for Office.

    public ChartOptions()
    {
        InitializeComponent();
    
        areaBlockChart.CheckedChanged += new EventHandler(areaBlockChart_CheckedChanged);
        barChart.CheckedChanged += new EventHandler(barChart_CheckedChanged);
        columnChart.CheckedChanged += new EventHandler(columnChart_CheckedChanged);
        lineChart.CheckedChanged += new EventHandler(lineChart_CheckedChanged);
    }
    
    

When you build the solution, the new user control is automatically added to the Toolbox. You can then drag the control from the Toolbox to your Worksheet.

To add the user control your worksheet

  1. On the Build menu, click Build Solution.

    The ChartOptions user control is added to the Toolbox.

  2. In Solution Explorer, right-click Sheet1.vb or Sheet1.cs and select View Designer.

  3. Drag the ChartOptions control from the Toolbox to the worksheet.

    A new control named ChartOptions1 is added to your project.

In this section, create an event handler to change the chart type according to the option selected in the user control.

To change the type of chart that is displayed in the worksheet

  1. Add the following event handler to the Sheet1 class.

    private void ChartOptions1_SelectionChanged(object sender, EventArgs e)
    {
        try
        {
            dataChart.ChartType = this.ChartOptions1.Selection;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }
    
    
  2. In C#, you must add an event handler for the user control to the Startup event as shown below. For information on creating event handlers, see How to: Create Event Handlers in Visual Studio Tools for Office.

    this.ChartOptions1.SelectionChanged += new EventHandler(ChartOptions1_SelectionChanged);
    
    

You can now test your workbook to make sure that the chart is styled correctly when you select a radio button.

To test your workbook

  1. Press F5 to run your project.

  2. Select various radio buttons.

  3. Confirm that the chart style changes to match the selection.

This walkthrough shows the basics of using radio buttons and chart styles on Excel 2003 worksheets. Here are some tasks that might come next:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2015 Microsoft