Add a check box, option button, or toggle button to a worksheet

You can use a check box, option button, or toggle button to indicate "either/or" (or binary) choices.

What do you want to do?



Learn about check boxes, option buttons, and toggle buttons

Check box    Turns on or off a value that indicates an opposite and unambiguous choice. You can select more than one check box at a time on a worksheet or in a group box. For example, you can use a check box to create an order form that contains a list of available items or in an inventory tracking application to show whether an item has been discontinued.

Check box (Form control)

Example of a Form check box control

Check box (ActiveX control)

Example of an ActiveX check box control


Option button    Allows a single choice from a limited set of mutually exclusive choices. An option button (or radio button) is usually contained in a group box or frame. For example, you can use an option button on an order form so that a user can select one of a range of sizes, such as small, medium, large, or extra large. Or you can use it for a choice of shipping options, such as ground, express, or overnight.

Option button (Form control)

Example of a Form option button control

Option button (ActiveX control)

Example of an ActiveX option button control


Toggle button    Indicates a state, such as Yes/No, or a mode, such as On/Off. The button alternates between an enabled and disabled state when it is clicked. For example, you can use a toggle button to switch between design mode and edit mode, or as an alternative to a check box.

 Note   The toggle button is not available as a Form control, only as an ActiveX control.

Toggle button (ActiveX control)

Example of an ActiveX toggle button control


Top of Page Top of Page

Add a check box (Form control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert and then, under Form Controls, click Check box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the control to appear.
  2. On the Developer tab, in the Controls group, click Properties Button image.

 Tip   You can also right-click the control, and then click Format Control.

To specify the control properties, do the following:

  1. Under Value, specify the initial state of the check box by doing one of the following:
    • To display a check box that is filled with a check mark, click Checked. A check mark indicates that the check box is selected.
    • To display a check box that is cleared, click Unchecked.
    • To display a check box that is filled with shading, click Mixed. Shading indicates a combination of selected and cleared states; for example, when there is a multiple selection.
  2. In the Cell link box, enter a cell reference that contains the current state of the check box:
    • When the check box is selected, the linked cell returns a TRUE value.
    • When the check box is cleared, the linked cell returns a FALSE value.

 Note   When the linked cell is empty, Microsoft Office Excel interprets the check box state as FALSE.

  • If the check box state is mixed, the linked cell returns a #N/A error value.

Use the returned value in a formula to respond to the current state of the check box.

For example, a travel survey form contains two check boxes labeled Europe and Australia in a Places traveled group box. These two check boxes are linked to cells C1 (for Europe) and C2 (for Australia).

When a user selects the Europe check box, the following formula in cell D1 evaluates to "Traveled in Europe":

=IF(C1=TRUE,"Traveled in Europe","Never traveled in Europe")

When a user clears the Australia check box, the following formula in cell D2 evaluates to "Never traveled in Australia":

=IF(C2=TRUE,"Traveled in Australia","Never traveled in Australia")

If you have three states to evaluate (Checked, Unchecked, and Mixed) in the same group of options, you can use the CHOOSE or LOOKUP functions similarly.

 Note   The size of the check box inside the control and its distance from its associated cannot be adjusted.

Top of Page Top of Page

Add a check box (ActiveX control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Check Box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the check box to appear.
  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  3. To specify the control properties, on the Developer tab, in the Controls group, click Properties Button image.

 Tip   You can also right-click the control, and then click Properties.

The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify Use this property
General:  
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events. Enabled (Form)
Whether the control can be edited. Locked (Form)
The name of the control. Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). Placement (Excel)
Whether the control can be printed. PrintObject (Excel)
Whether the control is visible or hidden. Visible (Form)
Text:  
The position of the control relative to its caption (left or right). Alignment (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight). Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
Descriptive text on the control that identifies or describes it. Caption (Form)
How text is aligned in the control (left, center, or right). TextAlign (Form)
Whether the contents of the control automatically wrap at the end of a line. WordWrap (Form)
Data and binding:  
The range that is linked to the control's value. LinkedCell (Excel)
The content or state of the control. Value (Form)
Size and position:  
Whether the size of the control automatically adjusts to display all the contents. AutoSize (Form)
The height or width in points. Height, Width (Form)
The distance between the control and the left or top edge of the worksheet. Left, Top (Form)
Formatting:  
The background color. BackColor (Form)
The background style (transparent or opaque). BackStyle (Form)
The foreground color. ForeColor (Form)
Whether the control has a shadow. Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump). SpecialEffect (Form)
Image:  
The bitmap to display in the control. Picture (Form)
The location of the picture relative to its caption (left, top, right, and so on). PicturePosition (Form)
Keyboard and mouse:  
The shortcut key for the control. Accelerator (Form)
A custom mouse icon. MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). MousePointer (Form)
Specific to check box:  
A group of mutually exclusive option buttons. GroupName (Form)
Whether a user can specify the Null state for the control from the user interface. TripleState (Form)

 Note   The size of the check box inside the control and its distance from its associated text cannot be adjusted.

Top of Page Top of Page

Add an option button (Form control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Option button Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the option button to appear.
  2. On the Developer tab, in the Controls group, click Properties Edit code button.

 Tip   You can also right-click the control, and then click Format Control.

To set the control properties, do the following:

  1. Under Value, specify the initial state of the option button by doing one of the following:
    • To display an option button that is selected, click Checked.
    • To display an option button that is cleared, click Unchecked.
  2. In the Cell link box, enter a cell reference that contains the current state of the option button.

The linked cell returns the number of the selected option button in the group of options. Use the same linked cell for all options in a group. The first option button returns a 1, the second option button returns a 2, and so on. If you have two or more option groups on the same worksheet, use a different linked cell for each option group.

Use the returned number in a formula to respond to the selected option.

For example, a personnel form, with a Job type group box, contains two option buttons labeled Full-time and Part-time linked to cell C1. After a user selects one of the two options, the following formula in cell D1 evaluates to "Full-time" if the first option button is selected or "Part-time" if the second option button is selected.

=IF(C1=1,"Full-time","Part-time")

If you have three or more options to evaluate in the same group of options, you can use the CHOOSE or LOOKUP functions in a similar manner.

 Notes 

  • You can also edit the control properties by selecting the control, and then by clicking Control Properties Button image on the Forms toolbar.
  • The size of the option button inside the control and its distance from its associated text cannot be adjusted.

 Note   To see an example of option buttons that are used in an Excel template, see the help topic, Electoral College calculator.

Top of Page Top of Page

Add an option button (ActiveX control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert and then, under ActiveX Controls, click Option Button Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the option button to appear.
  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  3. To set the control properties, on the Developer tab, in the Controls group, click Properties Button image.

 Tip   You can also right-click the control, and then click Properties.

The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify Use this property
General:  
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) AutoLoad (Excel)
The name of the control. Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). Placement (Excel)
Whether the control can be edited. Locked (Form)
Whether the control can be printed. PrintObject (Excel)
Whether the control can receive focus and respond to user-generated events. Enabled (Form)
Whether the control is visible or hidden. Visible (Form)
Text:  
Descriptive text on the control that identifies or describes it. Caption (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight). Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
How text is aligned in the control (left, center, or right). TextAlign (Form)
The position of the control relative to its caption (left or right). Alignment (Form)
Whether the contents of the control automatically wrap at the end of a line. WordWrap (Form)
Data and binding:  
The content or state of the control. Value (Form)
The range linked to the control's value. LinkedCell (Excel)
Size and position:  
The distance between the control and the left or top edge of the worksheet. Left, Top (Form)
The height or width in points. Height, Width (Form)
Whether the size of the control automatically adjusts to display all the contents. AutoSize (Form)
Formatting:  
The background color. BackColor (Form)
The background style (transparent or opaque). BackStyle (Form)
The foreground color. ForeColor (Form)
The visual appearance of the border (flat, raised, sunken, etched, or bump). SpecialEffect (Form)
Whether the control has a shadow. Shadow (Excel)
Image:  
The bitmap to display in the control. Picture (Form)
The location of the picture relative to its caption (left, top, right, and so on). PicturePosition (Form)
Keyboard and mouse:  
A custom mouse icon. MouseIcon (Form)
The shortcut key for the control. Accelerator (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). MousePointer (Form)
Specific to option button:  
A group of mutually exclusive option buttons. GroupName (Form)
Whether a user can specify the Null state for the control from the user interface. TripleState (Form)

 Note   The size of the option button inside the control and its distance from its associated text cannot be adjusted.

Top of Page Top of Page

Add a Toggle button (ActiveX control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Toggle Button Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the toggle button to appear.
  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  3. To set the control properties, on the Developer tab, in the Controls group, click Properties Button image.

 Tip   You can also right-click the control, and then click Properties.

The Properties dialog box is displayed. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

If you want to specify Use this property
General:  
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) AutoLoad (Excel)
Whether the control can be edited. Locked (Form)
The name of the control. Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). Placement (Excel)
Whether the control can be printed. PrintObject (Excel)
Whether the control can receive focus and respond to user-generated events. Enabled (Form)
Whether the control is visible or hidden. Visible (Form)
Text:  
Descriptive text on the control that identifies or describes it. Caption (Form)
How text is aligned in the control (left, center, or right). TextAlign (Form)
Whether the contents of the control automatically wrap at the end of a line. WordWrap (Form)
Data and Binding:  
The range linked to the control's value. LinkedCell (Excel)
The content or state of the control. Value (Form)
Size and Position:  
Whether the size of the control automatically adjusts to display all the contents. AutoSize (Form)
The height or width in points. Height, Width (Form)
The distance between the control and the left or top edge of the worksheet. Left, Top (Form)
Formatting:  
The background color. BackColor (Form)
The background style (transparent or opaque). BackStyle (Form)
The foreground color. ForeColor (Form)
Whether the control has a shadow. Shadow (Excel)
Image:  
The bitmap to display in the control. Picture (Form)
The location of the picture relative to its caption (left, top, right, and so on). PicturePosition (Form)
Keyboard and Mouse:  
The shortcut key for the control. Accelerator (Form)
A custom mouse icon. MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). MousePointer (Form)
Specific to Toggle Button:  
Whether a user can specify the Null state for the control from the user interface. TripleState (Form)

Top of Page Top of Page

 
 
Applies to:
Excel 2007