- 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:
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
Using and Grouping Option Buttons
Option buttons operate as a group, but the spreadsheet user can select only one at a time. If you need to create multiple sets of buttons to manage different settings on a worksheet, you can place each set inside a group box to isolate it from the other sets.
To see a group of option buttons at work, choose Developer > Insert > Group Box, and then drag a group box onto the worksheet. Click Option Button, and drag to add an option button inside the group box. Repeat to add two more option buttons inside the group box.
Right-click one of the option button controls, and click Format Control. You can configure an option button to be checked or unchecked. Since only one can be checked at a time, configuring any of the buttons as checked will automatically make all of the others unchecked. In addition, whatever cell you set as the 'Cell link' for one option button in the group box will be the same for all of the option buttons. To change the label for an option button, right-click the option button and choose Edit Text.
Option buttons return a value in the 'Cell link' cell according to the order in which you created them, so the first one will return a value of 1 when clicked, the second one will return the value 2, the third the value 3, and so on. You can use the values you get from the option buttons to perform an action. For example, you might use option buttons with text reading 'Overnight', '2-Day', and 'Ground' on an invoice for a user to select how a delivery should be shipped. You could then write an IF function to make a calculation based on the user's selection.
So, if you added the option buttons in the order 'Overnight', '2-Day', and 'Ground', and if you made their 'Cell link' cell A1, you could type the following formula in any cell to return a shipping cost of $45 if Overnight is selected, $35 for 2-Day, and $20 for Ground:
Using a Checkbox to Select an Item