When you’re creating an application in Microsoft Excel often times you will want to give your user a way to enter values in a cell that does not involve them having to type them. Spinners and scrollbars are two valuable features in Excel 2007 that you can use to turn data entry into something that can be done with a click of the mouse. In this article, we’ll show you how to create a spreadsheet solution using spinners and scrollbars for data entry.
Spinners and scrollbars are Form tools and they
’
re accessible from the Developer toolbar which is hidden, by default, in Excel 2007. To display it, click the Office button and choose the
Excel Options > Show Developertab in the Ribbon, and click OK.
» Create the Worksheet
So that you have a worksheet to work with, create a worksheet that looks like the one in the figure. To do this, enter these values into these cells:
Cell: Value:
C3 Principal
C4 Interest Rate
C5 Payments per Year
C6 Number of Years
C7 Period Payment
D3 200,000
D4 5%
D5 12
D6 25
D7 =ABS(PMT(D4/D5,D6*D5,D3))This calculator helps you determine the period payment on a mortgage given the principal borrowed, the interest rate, the number of payments per year, and the number of years that the mortgage is amortized over.
» Configure the Spinner
If you want to share this workbook, your users will have to click in the cells in column D to enter their data. It would be better if we made it easier for them to use.
To do this, click in cell E6, select the Developer tab, click Insert and click Spin Button (Form Control) tool — you must use form controls rather than ActiveX controls, as these are something very different.
Drag the Spinner box into cell E6 so it can be used to adjust the number of years. To do this, right click the spinner control and choose Format Control > Control tab. Set the Current Value to 25, the Minimum Value to 5, the Maximum Value to 40, and the Incremental Change to 1.
Click in the Cell Link area, type D6 and click OK. This links the spinner control output to cell D6 so that when you click either arrow, the value the spinner returns is placed in D6.
The spinner has been set so it returns a value in the range 5 to 40 and the incremental change value controls the increment when you click either arrow — in our case the value increases or decreases by one.
This is a simple example as there is a direct relationship between the Spinner output and the range of values needed to be inserted into cell D6.
Add a second spinner to cell E3, right-click the Spinner, choose
Format Control > Controltab. We have a problem with this spinner
’
s settings in that the principal for a typical mortgage is far larger than the allowable spinner values, which are between 0 and 30,000.
The solution is to place the value that the spinner returns in a second cell and to use this value in a calculation so we can create the desired range in cell D3. If we want mortgage principal values between 50,000 and 500,000 we can set the spinner’s minimum value to 500, the maximum value to 5,000 and multiply these values by 100 to get the desired range.
In addition, we’ll set the incremental change to 10 so that ultimately, when we multiply that by 100 we’ll have the mortgage principal incrementing by 1,000 for every click of the spinner. Set the Cell Link to F3 and click OK.
To finish this spinner, click in cell D3 and enter this formula:
=F3*100
When you adjust the spinner, the value in cell F3 adjusts and, as a result, the principal value in cell D3 recalculates with each change.
Typically when you use spinners and scrollbars you’ll need to make adjustments like this to ensure that the value that you get from the spinner or scrollbar can be used on your worksheet.