Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

Article
03/10/2023

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:

An edition of Visual Studio that includes the Microsoft Office developer tools. For more information, see Configure a computer to develop Office solutions.
Excel 2013 or Excel 2010 .

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

Open Microsoft Excel.
Right-click the Sheet3 tab, and then click Rename on the shortcut menu.
Rename the sheet to Data for Chart.
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.

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

To add a chart in Excel

On the Insert tab, in the Charts group, click Column, and then click All Chart Types.
In the Insert Chart dialog box, click OK.
On the Design tab, in the Data group, click Select Data.
In the Select Data Source dialog box, click in the Chartdata range box and clear any default selection.
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.
In the Select Data Source dialog box, click OK.
Reposition the chart so that the upper right corner aligns with cell E2.
Save your file to drive C and name it ExcelChart.xlsx.
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

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.
Click the Browse button and browse to the workbook you created earlier in this walkthrough.
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

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

Property Value

Name dataChart

HasLegend false

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

Select the My Excel Chart project in Solution Explorer.
On the Project menu, click Add New Item.
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

If the user control is not visible in the designer, double-click ChartOptions in Solution Explorer.
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
Add a second radio button to the user control, and change the following properties.

Property Value

Name barChart

Text Bar Chart
Add a third radio button to the user control, and change the following properties.

Property Value

Name lineChart

Text Line Chart
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.

Property	Value
Name	columnChart
Text	Column Chart

Property	Value
Name	barChart
Text	Bar Chart

Property	Value
Name	lineChart
Text	Line Chart

Property	Value
Name	areaBlockChart
Text	Area Block Chart

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

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

Add code to the ChartOptions class to create a SelectionChanged event and the Selection 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;
    }
}

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

To handle the CheckedChanged event of the radio buttons

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

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

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

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

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

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

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

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

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

On the Build menu, click Build Solution.

The ChartOptions user control is added to the Toolbox.
In Solution Explorer, right-click Sheet1.vb or Sheet1.cs, and then click View Designer.
Drag the ChartOptions control from the Toolbox to the worksheet.

A new control named my_Excel_Chart_ChartOptions1 is added to your project.
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

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

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

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

Press F5 to run your project.
Select various radio buttons.
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:

Deploying the project. For more information, see Deploy an Office solution.
Using a button to populate a text box. For more information, see Walkthrough: Display text in a text box in a worksheet using a button.
Change formatting on a worksheet by using check boxes.

Walkthroughs using Excel

Share via

Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

Prerequisites

Add a chart to a worksheet

To add the data

To add a chart in Excel

Create a new project

To create a new project

Set properties of the chart

To change the name of the Chart control

Add controls

To add a user control

To add radio buttons to the user control

Change the chart style when a radio button is selected

To create an event and property on a user control

To handle the CheckedChanged event of the radio buttons

Add the user control to the worksheet

To add the user control your worksheet

Change the chart type

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

Test the application

To test your workbook

Next steps

Feedback

Additional resources

Share via

Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

Prerequisites

Add a chart to a worksheet

To add the data

To add a chart in Excel

Create a new project

To create a new project

Set properties of the chart

To change the name of the Chart control

Add controls

To add a user control

To add radio buttons to the user control

Change the chart style when a radio button is selected

To create an event and property on a user control

To handle the CheckedChanged event of the radio buttons

Add the user control to the worksheet

To add the user control your worksheet

Change the chart type

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

Test the application

To test your workbook

Next steps

Related content

Feedback

Additional resources