» 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.
Into cell D4 type this formula:
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:
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!|