Simplifying Excel Form

Controls: Check Box,

Option Button, Spin

Button, and Scroll Bar

Excel form controls provide interactivity and convenience to an Excel worksheet. With form controls a user can quickly run through scenarios, add or subtract large pieces of formulation, and quickly scroll through a large range of values within a single cell. The most commonly used form controls are the Check Box, the Option Button, the Spin Button, and the Scroll Bar. Here is a brief description of what each does:

Check Box – The Check Box toggles the value in a specified cell between TRUE and FALSE. This enables a user to turn on or turn off large blocks of formulation by simply checking or unchecking a check box. Utilizing If-Then-Else statements linked to the TRUE/FALSE cell is a common way of toggling on or off the inclusion of blocks of formulation or individual items. This will be shown in this article. An example using multiple Excel check boxes is as follows:

(Click On Image To See a Larger Version)

Option Button – Formerly called a Radio Button, the Option Button is used to switch between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article. An example using options buttons to implement scenario selection is shown as follows:

(Click On Image To See a Larger Version)

Spin Button – Clicking a Spin Button increases or decreases the value in a specified cell by an incremental amount that was defined when the spin button was initially created and formatted. An example of a spin button in use is shown as follows:

(Click On Image To See a Larger Version)

Scroll Bar – Scrolling up or down with a scroll bar increases or decreases the value in a specified cell in a convenient and rapid fashion. An example of a scroll bar in use is shown as follows:

(Click On Image To See a Larger Version)

 

Turning On the Developer Tab in the Excel Ribbon

Form controls are accessed from the Developer tab. By default the Developer tab does not appear in the ribbon. The Developer tab must be configured to be one of the tabs permanently displayed in the ribbon. This can be quickly implemented with the following steps:

File / Options / Customize Ribbon

This will produce the following dialogue box. The checkbox next to Developer will initially be unchecked. Simply check that checkbox and the Developer tab will become a permanently visible part of the ribbon as long as that checkbox remains checked. This is shown in the following images.

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The Developer tab now appears as a permanent part of the ribbon as long as the checkbox next to Developer remains checked. This is shown as follows:

(Click On Image To See a Larger Version)

All form controls are made available clicking Insert in the Developer tab.

(Click On Image To See a Larger Version)

 

Check box

The check box toggles the value in a specified cell between TRUE and FALSE. This enables a user to turn on or turn off large blocks of formulation by simply checking or unchecking a check box. Each check box is independent of the other check boxes. Utilizing If-Then-Else statements linked to the TRUE/FALSE cell is a common way of toggling on or off the inclusion of blocks of formulation or individual items.

The checkbox is creating by clicking the check box icon in the Form Controls menu as follows:

(Click On Image To See a Larger Version)

When the check box icon is clicked, the cursor become the tool to place the check box on the Excel worksheet. The outline of the check box is created by clicking and then dragging the cursor. The location and dimensions of the check box can be changed at any time after the initial check box has been created. The initial check box will appear as follows:

(Click On Image To See a Larger Version)

The check box must now be formatted. This is accomplished by right-clicking anywhere in the check box to bring up the following short-cut menu. Format Control is selected from the short-cut menu as follows:

(Click On Image To See a Larger Version)

The following Format Control dialogue box then appears.

(Click On Image To See a Larger Version)

Type in the cell that will contain the check box’s output, which toggles between TRUE and FALSE. Selecting Unchecked specifies that check box will initially be unchecked on the worksheet as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

If desired, the check box can be outlined and colored with the Format Control dialogue box as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

One of the most common uses of the check box is to toggle between inclusion and exclusion of items within computation. An If-Then-Else statement linked to the TRUE/FALSE cell is a common method to implement this toggle as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

 

Option Button

Formerly called a Radio Button, the Option Button is used to switch between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are common methods employed to implement scenario selection. Both of these techniques will be demonstrated in this article.

The option button is creating by clicking the option button icon in the Form Controls menu as follows:

(Click On Image To See a Larger Version)

When the option button icon is clicked, the cursor become the tool to place the option button on the Excel worksheet. The outline of the option button is created by clicking and then dragging the cursor. The location and dimensions of the option button can be changed at any time after the initial option button has been created. The initial option button will appear as follows:

(Click On Image To See a Larger Version)

The option button must now be formatted. This is accomplished by right-clicking anywhere in the option button to bring up the following short-cut menu. Format Control is selected from the short-cut menu. This brings up the Format Control dialogue box. The cell which will hold the output of the option button must now be specified. In this case that cell is A4.

Each new option button that is created will be part of the initial group of option buttons. All option buttons in the same group will collectively send their output to the same cell. Only one option button in a group can selected at any time. The output cell, A4 in this case, will contain the number that is associated with and unique to the specific option button that has been selected.

(Click On Image To See a Larger Version)

The first completed options button and output cell appear as follows

(Click On Image To See a Larger Version)

A second options button can now be created. This second options button will be part of the same group that contains the first options button. The Format Control dialogue box that will appear when the second dialogue box is right-clicked as follows. By default the value of the second options buttons will initially be Unchecked and the output will be linked to the same output cell of the first options box, which is A4.

Changing the output cell in the Format Control of an options button will change the location of the output cells for all options buttons in the same group.

(Click On Image To See a Larger Version)

A third options button can be added to the group as follows:

(Click On Image To See a Larger Version)

The labels associated with each options button can be renamed by right-clicking on the original label and then typing in the new label. The result might be as follows:

(Click On Image To See a Larger Version)

These three options buttons can be assigned to a single group by selecting the Group Box from the list of Format Controls.

(Click On Image To See a Larger Version)

When the group box icon is clicked, the cursor become the tool to place the group box on the Excel worksheet. The outline of the group box is created by clicking and then dragging the cursor. The location and dimensions of the check box can be changed at any time after the initial check box has been created. Any options buttons that are inside the group box are part of one group. Any options buttons that are outside of the group box are part of a different group. The initial group box will appear as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The Option Button is designed to create the ability to choose between multiple options. This quite commonly used to perform scenario analysis. Linking If-Then-Else statements or the CHOOSE() formula to the options buttons are straightforward methods to implement scenario selection. An example of each of the two methods are shown as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

Conditional formatting was applied to display negative values in red instead of black as shown as follows:

(Click On Image To See a Larger Version)

 

Spin Button

Clicking a Spin Button increases or decreases the value in a specified cell by an incremental amount that was defined when the spin button was initially created and formatted.

The spin button is creating by clicking the spin button icon in the Form Controls menu as follows:

(Click On Image To See a Larger Version)

When the spin button icon is clicked, the cursor become the tool to place the spin button on the Excel worksheet. The outline of the spin button is created by clicking and then dragging the cursor. The location and dimensions of the spin button can be changed at any time after the initial spin button has been created. The initial spin button will appear as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The spin button must now be formatted. This is accomplished by right-clicking anywhere in the spin button to bring up the following short-cut menu. Format Control is selected from the short-cut menu. This brings up the Format Control dialogue box. The cell which will hold the output of the spin button must now be specified. In this case that cell is A4. The range, incremental change, and initial value of the spin button are declared as follows:

(Click On Image To See a Larger Version)

The completed spin button and output cell appear as follows:

(Click On Image To See a Larger Version)

Clicking an up or down arrow changes the value in the output cell by one increment if the new value is within the range specified in the Format Control dialogue box. 19 clicks of the up arrow would produce this value in the output cell:

(Click On Image To See a Larger Version)

 

Scroll Bar

Scrolling up or down with a scroll bar increases or decreases the value in a specified cell in a convenient and rapid fashion.

The scroll bar is creating by clicking the scroll bar icon in the Form Controls menu as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The scroll bar must now be formatted. This is accomplished by right-clicking anywhere in the scroll bar to bring up the following short-cut menu. Format Control is selected from the short-cut menu. This brings up the Format Control dialogue box. The cell which will hold the output of the scroll bar must now be specified. In this case that cell is A4. The range, incremental change, and initial value of the scroll bar are declared as follows:

(Click On Image To See a Larger Version)

(Click On Image To See a Larger Version)

The completed scroll bar and output cell appear as follows:

(Click On Image To See a Larger Version)

Scrolling up or down with the scroll bar rapidly changes the output cells value as follows:

(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!