Read on, and I’ll show you how to accomplish these and several other cool Excel tricks.
Locating the Tools You'll Need to Use
Click the Developer tab on the Ribbon, and select Insert (within the Developer tab--don’t click the Ribbon’s Insert tab) to view the Form Controls collection. Hover your mouse over each icon, and a text box will display its name and function. Note that the ActiveX Controls panel uses similar--in some cases, identical--icons, but they perform very different tasks. Be sure to click only Form Controls items for the purposes of following along with this article.
You can add Form Controls items to your worksheets to introduce click-and-drag functionality. An option button, for example, can run a macro when the spreadsheet user clicks it. The combo box and list box controls let the user choose items from a list, and the user can click checkboxes and option buttons to select the items they represent. The user can drag or click spin buttons and scrollbars to increase or decrease a value, too. In this article, I'll look at how to use the spin button, scrollbar, option button, and checkbox controls.
Starting With the Spin Button Form Control
Now, right-click the spin button, choose Format Control, and click the Control tab to configure the control. The 'Current value' is the starting value for the control. The 'Minimum value' and 'Maximum value' are, respectively, the smallest and largest values the control can provide (limited to between 0 and 30,000). 'Incremental change' refers to the amount by which the value changes each time the spreadsheet user clicks an arrow. The 'Cell link' is the cell in which the spin button will place its value.
Type the following values:
- Current value: 50
- Minimum value: 0
- Maximum value: 400
- Incremental change: 25
- Cell link: A1
Click OK, and then click outside the spin button to deselect it.
To test the spin button, use its arrows: Click its up arrow to increment the value in cell A1 by 25, and click its down arrow to decrement the value by 25. Note that the value can never increase beyond 400, which is the maximum value you set for it. By the same token, it cannot decrease below its minimum value of 0.
Exploring the Additional Functionality of Scrollbars
The scrollbar has all the same settings as the spin button does, but it also has a 'Page change' value, the value by which the number in the 'Cell link' cell changes when the user clicks the scrollbar. The 'Incremental change' refers to the value that is added or removed when the user clicks the arrow at either end of the scrollbar. Of course, the scrollbar also has a slider that the user can drag to change the value; this comes in handy when the user needs to make large value changes.
Both the scrollbar and spin button can produce a value only between 0 and 30,000. This might seem like a severe limitation, but you can use a formula to obtain a much larger range of values. For example, create a scrollbar, right-click it, and choose Format Control > Control.
Type the following values for its settings:
- Current value: 0
- Minimum value: 0
- Maximum value: 100
- Incremental change: 1
- Page change: 10
- Cell link A3
Click OK and then click outside the scrollbar to deselect it.
Next, click in cell B3 and type this formula:
=A3-50
Now when the user clicks the scrollbar, the value in cell A3 will always be in the range 0 to 100. The cell next to it, however--cell B3--will display a value between -50 and +50 because of the formula it contains. If you hide the value in cell A3 (by setting it to show white text on a white background, for example), the user will see only the scrollbar changing the value in cell B3.
Next Page: Using and Grouping Option Buttons