How to create advanced Microsoft Excel spreadsheets

Learn how to control Excel worksheets with spin buttons, scrollbars, option buttons, and checkboxes.

By Helen Bradley, PC World |  Software, Excel, how-to

One of the easiest controls to understand is the spin button, which lets the spreadsheet user increase or decrease the value in a cell by clicking on it. To see how it works, click Developer > Insert > Spin Button, and then drag (hold your left mouse button down) to create a spin button within the worksheet. The spin button control has large arrows at the top and bottom of a box.

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 works in a similar way to the spin button. Click Developer > Insert > Scroll Bar to select this control. Now drag vertically or horizontally on the worksheet to create a vertical or horizontal scrollbar. Both work the same way; they just have different orientations. Now, right-click the scrollbar, choose Format Control, and click the Control tab to set its options.

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:

Originally published on PC World |  Click here to read the original story.
Join us:






SoftwareWhite Papers & Webcasts

Webcast On Demand

HP DevOps KnowledgeVault

Sponsor: HP

See more White Papers | Webcasts

Answers - Powered by ITworld

Ask a Question