In the example I've set the cell link cell to E2 and the formula in C2 is =E2*10000 - this is the cell that shows the desired loan amount. You can use a scrollbar for entering the loan amount. If you want to scale the value, you'll need to use a second cell with a formula in it that will scale the value that the Scroll Bar gives you appropriately for the end result that you need.įor example, consider an organization that makes loans of anything from $20,000 to $5,000,000 in multiples of $10,000. With a Scroll Bar you must, of course, set a Cell Link for the value to be placed in. The Incremental Change value is applied when you click on the arrows at either end of the Scroll Bar. Whenever you need to produce a value more complex than an integer between 0 use a solution like this to scale the value you get from the Spin Button to get your desired value.Ī Scroll Bar works in a similar way to the Spin Button except that with a Scroll Bar you can set a Page Change value which is the change in value when you click on the Scroll Bar either side of its moving marker. The value in cell A1 is being created by the Spin Button but it is the value in cell A2 that you are most interested in. Now as you click on the Spin Button you'll see that the value in cell A2 gives you what you want - a percentage value between 0 and 5% in increments of 0.1%. Now, in cell A2 type the formula =A1/10000, and format A2 using a percentage format and one decimal place. Here is one way to get your Spin Button to do this: right click it and set the Minimum Value to 0, the Maximum Value to 500 and the Incremental Change to 10. There are any number of ways to do the mathematics for this and it doesn't really matter how you do it as long as your solution works. So, for example, if you want a user to enter a value between 0 percent and 5 percent in steps of 0.1% you'll need to scale the value the Spin Button gives you so you get a range of values from 0 to. The answer is to use an intermediate cell to make the calculations for you. However, you might be wondering what you can do when the values they are to enter are not whole numbers between 0 and 30,000?
You can use a Spin Button like this to make it easy for a user to enter a value into a worksheet by clicking a button rather than typing a value. Notice that the value changes in steps of 10. You can increase the value to 300 but not any higher and decrease it to 0 but not any lower. As you do, you'll see the value in cell A1 change.
Click in the Cell Link box, click on cell A1 and close the dialog.Ĭlick away from the Spin Button to deselect it and now click the arrows on the Spin Button. The limits for the Spin Button are that the value is to be a whole number between 0 and 30,000 and the Incremental Change can be any whole number between 1 and 30,000.įor now, set the Current Value to 50, Minimum Value to 0, Maximum Value to 300 and the Incremental Change to 10. You click the arrows to increase or decrease the value in the cell. Essentially what the Spin Button does is to place a value in a cell for you. In the Control tab are your options for configuring the Spin Button. To see how these work, right click the Spin Button and choose Format Control. To move or resize a control, right click it to select it and then size or move as required. Notice that you can drag your scrollbars so they are oriented either vertically or horizontally. It's vital that you select those in the Form Controls group not the ActiveX Controls as they work very differently and it's the Form Controls that we are using.ĭrag a Spin Button onto your worksheet and drag a Scroll Bar onto your worksheet too.
To see the tools, choose Developer > Insert and then select either the Spin Button or the Scroll Bar tool from the Form Controls group. In Excel 2007 click the Office button, choose Excel Options and then enable the Show Developer tab in the Ribbon checkbox in the Popular group. If you don't have this visible, in Excel 2010, choose File > Options > Customize Ribbon and in the second panel make sure that the Developer checkbox is selected.
In Excel 2007 and Excel 2010, the spin buttons and scroll bars are accessible from the Developer tab. Today, I'll introduce you to using spin buttons and scroll bars and show you an way to add an interactive element to an Excel chart with a spin button. You can do this in a number of ways and one of them is to use a spin button or a scroll bar to do the work. Whenever your user has a discrete number of choices to make for inputting data into an Excel worksheet you can save time by automating how they enter this data.
#Tabs and horizontal scroll bar missing in excel 2007 how to
Helen Bradley explains how to add interactive elements to an Excel worksheet using scroll bars and spin buttons.
0 kommentar(er)
