Share via

Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

This walkthrough shows the basics of using radio buttons on a Microsoft Office Excel worksheet to give the user a way to quickly switch between options. In this case, the options change the style of a chart.

Applies to: The information in this topic applies to document-level projects for Excel. For more information, see Features available by Office application and project type.

To see the result as a completed sample, see the Excel Controls Sample at Office development samples and walkthroughs.

This walkthrough illustrates the following tasks:

  • Adding a group of radio buttons to a worksheet.

  • Changing the chart style when an option is selected.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalize the Visual Studio IDE.

Prerequisites

You need the following components to complete this walkthrough:

Add a chart to a worksheet

You can create an Excel Workbook project that customizes 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 Microsoft Excel.

  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.

    Region / Quarter 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 a chart to the first worksheet to display the data.

To add a chart in Excel

  1. On the Insert tab, in the Charts group, click Column, and then click All Chart Types.

  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 block of cells that contains the numbers, which includes A4 in the upper left corner to E8 in the lower right corner.

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

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

  8. Save your file to drive C and name it ExcelChart.xlsx.

  9. Exit Excel.

Create a new project

In this step, you will create an Excel Workbook project based on the ExcelChart workbook.

To create a new project

  1. Create an Excel Workbook project with the name My Excel Chart. In the wizard, select Copy an existing document.

    For more information, see How to: Create Office projects in Visual Studio.

  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.

Set properties of the chart

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

To change the name of the Chart control

  1. Select the Chart control in the designer and change the following properties in the Properties Window.

    Property Value
    Name dataChart
    HasLegend false

Add controls

This worksheet uses radio buttons to give users a way to quickly change the chart style. However, radio buttons need to be exclusive—when one button is selected, no other button in the group can be selected at the same time. This behavior does not happen by default when you add several radio buttons to a worksheet.

One way to add this behavior is to group the radio buttons on a user control, write your code behind the user control, and then add the user control to the worksheet.

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 radio buttons 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.

Change the chart style when a radio button is selected

Now you can add the code to change the chart style. To do this, 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 then click View Code.

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

    Public Event SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

Private selectedType As Microsoft.Office.Interop.Excel.XlChartType = _
    Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered

Public Property Selection() As Microsoft.Office.Interop.Excel.XlChartType
    Get
        Return Me.selectedType
    End Get
    Set(ByVal value As Microsoft.Office.Interop.Excel.XlChartType)
        Me.selectedType = value
    End Set
End Property

    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 Sub areaBlockChart_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles areaBlockChart.CheckedChanged

    If (CType(sender, RadioButton).Checked) Then

        Me.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlAreaStacked
        RaiseEvent SelectionChanged(Me, EventArgs.Empty)
    End If
End Sub

    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 Sub barChart_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles barChart.CheckedChanged

    If (CType(sender, RadioButton).Checked) Then

        Me.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered
        RaiseEvent SelectionChanged(Me, EventArgs.Empty)
    End If
End Sub

    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 Sub columnChart_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles columnChart.CheckedChanged

    If (CType(sender, RadioButton).Checked) Then

        Me.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered
        RaiseEvent SelectionChanged(Me, EventArgs.Empty)
    End If
End Sub

    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 Sub lineChart_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles lineChart.CheckedChanged

    If (CType(sender, RadioButton).Checked) Then

        Me.selectedType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers
        RaiseEvent SelectionChanged(Me, EventArgs.Empty)
    End If
End Sub

    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. For information about how to create event handlers, see How to: Create event handlers in Office projects.

    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);
}

Add the user control to the worksheet

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 then click View Designer.

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

    A new control named my_Excel_Chart_ChartOptions1 is added to your project.

  4. Change the name of the control to ChartOptions1.

Change the chart type

To change the chart type, create an event handler that sets the style 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 Sub ChartOptions1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles ChartOptions1.SelectionChanged

    Try
        dataChart.ChartType = Me.ChartOptions1.Selection
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

    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 about how to create event handlers, see How to: Create event handlers in Office projects.

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

Test the application

You can now test your workbook to verify 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.

Next steps

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

See also