Walkthrough: Changing Worksheet Formatting Using CheckBox Controls

This walkthrough shows the basics of using check boxes on a Microsoft Office Excel worksheet to change formatting. You will use Office development tools in Visual Studio to create and add code to your project. To see the result as a completed sample, see the Excel Controls Sample at Office Development Samples and Walkthroughs.

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.

During this walkthrough, you will learn how to:

• Add text and controls to a worksheet.

• Format the text when an option is selected.

• Test your project.

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 Customizing Development Settings in Visual Studio.

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.

Creating the Project

In this step, you will create an Excel Workbook project by using Visual Studio.

To create a new project

• Create an Excel Workbook project with the name My Excel Formatting. Make sure that Create a new document is selected. For more information, see How to: Create Office Projects in Visual Studio.

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

Adding Text and Controls to the Worksheet

For this walkthrough, you will need three CheckBox controls and some text in a NamedRange control.

To add three check boxes

1. Verify that the workbook is open in the Visual Studio designer and that Sheet1 is open.

2. From the Common Controls tab of the Toolbox, drag a CheckBox control to or near cell B2 in Sheet1.

3. From the View menu, select Properties window.

4. Be sure that Checkbox1 is visible in the object name list box of the Properties window, and change the following properties:

   Property	Value
   Name	applyBoldFont
   Text	Bold

5. Drag a second check box on or near cell B4 and change the following properties:

   Property	Value
   Name	applyItalicFont
   Text	Italic

6. Drag a third check box on or near cell B6 and change the following properties:

   Property	Value
   Name	applyUnderlineFont
   Text	Underline

7. Select all three check box controls while holding the CTRL key.

8. In the Arrange Group of the Format tab in Excel, click Align, and then click Align Left.

   The three check box controls are aligned on the left side, at the position of the first control you selected.

   Next, you will drag a NamedRange control to the worksheet.

   Note

   You can also add the NamedRange control by typing textFont into the Name box.

To add text to a NamedRange control

1. From the Excel Controls tab of the toolbox, drag a NamedRange control to cell B9.

2. Verify that $B$9 appears in the editable text box, and that cell B9 is selected. If it is not, click cell B9 to select it.

3. Click OK.

4. Cell B9 becomes a range named NamedRange1.

   There is no visible indication on the worksheet, but NamedRange1 appears in the Name box (just above the worksheet on the left side) when cell B9 is selected.

5. Be sure that NamedRange1 is visible in the object name list box of the Properties window, and change the following properties:

   Property	Value
   Name	textFont
   Value2	Click a check box to change the formatting of this text.

Next, write the code to format the text when an option is selected.

Formatting the Text When an Option is Selected

In this section, you will write code so that when the user selects a formatting option, the format of the text in the worksheet is changed.

To change formatting when a check box is selected

1. Right-click Sheet1, and then click View Code on the shortcut menu.

2. Add the following code to the Click event handler of the applyBoldFont check box:

   Private Sub applyBoldFont_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
       Handles applyBoldFont.Click
   
       Me.textFont.Font.Bold = Me.applyBoldFont.Checked
   End Sub
   

   private void applyBoldFont_Click(object sender, EventArgs e)
   {
       this.textFont.Font.Bold = this.applyBoldFont.Checked;
   }
   

3. Add the following code to the Click event handler of the applyItalicFont check box:

   Private Sub applyItalicFont_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
       Handles applyItalicFont.Click
   
       Me.textFont.Font.Italic = Me.applyItalicFont.Checked
   End Sub
   

   private void applyItalicFont_Click(object sender, EventArgs e)
   {
       this.textFont.Font.Italic = this.applyItalicFont.Checked;
   }
   

4. Add the following code to the Click event handler of the applyUnderlineFont check box:

   Private Sub applyUnderlineFont_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
       Handles applyUnderlineFont.Click
   
       If Me.applyUnderlineFont.Checked Then 
           Me.textFont.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
       Else 
           Me.textFont.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone
       End If 
   End Sub
   

   private void applyUnderlineFont_Click(object sender, EventArgs e)
   {
       if (this.applyUnderlineFont.Checked == true)
       {
           this.textFont.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle;
       }
       else
       {
           this.textFont.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone;
       }
   }
   

5. In C#, you must add event handlers for the check boxes to the Startup event as shown below. For information on creating event handlers, see How to: Create Event Handlers in Office Projects.

   this.applyBoldFont.Click += new EventHandler(applyBoldFont_Click);
   this.applyItalicFont.Click += new EventHandler(applyItalicFont_Click);
   this.applyUnderlineFont.Click += new EventHandler(applyUnderlineFont_Click);
   

Testing the Application

You can now test your workbook to make sure that the text is formatted correctly when you select or clear a check box.

To test your workbook

1. Press F5 to run your project.

2. Select or clear a check box.

3. Confirm that the text is formatted correctly.

Next Steps

This walkthrough shows the basics of using check boxes and formatting text on Excel worksheets. Here are some tasks that might come next:

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

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

See Also

Concepts

Walkthroughs Using Excel

NamedRange Control

Limitations of Windows Forms Controls on Office Documents