Exploring Office 2007: Improving Data Entry with Scrollbars and Spinners in Excel 2007 Using Scrollbar Controls in Excel 2007 Helen Bradley
»
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.
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.
Be sure to check out all of Helen's articles in the Exploring Office 2007 series:
