How to Use Scrollbars and Spinners in Excel 2007 - Page 2

» Using Scrollbars

Now let's use a scrollbar to change the interest rate value. Click the Scrollbar Form Tool and drag a horizontal scrollbar in cell E4. Right click it and choose Format Control. The scrollbar has all the same settings as the Spinner control and a Page Change value.

The Page Change value is the amount that the value changes when you click either side of the marker in the scrollbar. Typically, clicking here moves you a larger value than when you click the scrollbar arrows.

Using the CEILING function ensures values returned by the scrollbar are always multiples of 0.05%

To get a usable interest rate value we will need to place the scrollbar value in another cell and use the value it returns in a calculation to get interest rates in the range 0.5% to 10% (which is the numerical range of 0.005 to 0.1) and an increment of 0.05%. So configure the Scrollbar with these properties: Minimum Value 50, Maximum Value 1,000, Incremental Change 5, Page Change 10, and set the Cell Link to cell F4.

Into cell D4 type this formula:

=F4/10000

This will return a value between 0.5% and 10% with an increment of 0.05% and a page change of 0.1%. When you test this, also test the scrollbar by dragging the middle slider and you will encounter a small problem in that the interest rates don't always appear as multiples of 0.05%. You can solve the problem by replacing the formula in cell D4 with this formula:

=CEILING(F4,5)/10000

This formula rounds the value returned by the scrollbar upwards to the nearest multiple of 5 before converting it into the desired interest rate value.

» Tidying Up

When you are done creating your scrollbars and spinners, hide column F by selecting the column letter, right clicking, and choosing Hide. Now the interim values won't confuse your user.

Next time you are developing an application in Excel give consideration to whether the Spinners and Scrollbars feature in Excel 2007 will provide your users with a smarter way to enter data than by typing it.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.

Adapted from winplanet.com.

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!