Walkthrough: Updating a Chart in a Worksheet Using Radio Buttons

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 2013 and Excel 2010. 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.

Prerequisites

You need the following components to complete this walkthrough:

• An edition of Visual Studio 2013 that includes the Microsoft Office developer tools. For more information, see Configuring a Computer to Develop Office Solutions.

• Excel 2013 or Excel 2010.

Adding 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.

   	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.

Creating 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 buttonand 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.

Setting 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

Adding 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.

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

Adding 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.

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

Testing 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:

• Deploying the project. For more information, see Deploying an Office Solution.

• Using a button to populate a text box. For more information, see Walkthrough: Displaying Text in a Text Box in a Worksheet Using a Button.

• Change formatting on a worksheet by using check boxes. For more information, see Walkthrough: Changing Worksheet Formatting Using CheckBox Controls.

See Also

Concepts

Walkthroughs Using Excel