Excel's Best-Kept Secret What? You're Not Using the Forms Toolbar? Helen Bradley
Wed 7/10/02 -- What's in your Excel toolbar? Chances are, you have the spreadsheet's Standard and Formatting toolbars at the top of your screen, maybe the Chart and Drawing toolbars if you're artistically inclined. But check the View/Toolbars menu and you'll discover a Forms toolbar stacked with an array of nifty spinners, checkboxes, option buttons, and combo boxes. Check out Help to learn what you can do with them, however, and you'll find there's not much information available -- while the Forms tools have been around for several years and versions of Excel, precious few people know they exist, and fewer still use them. Until now! This week I'll show you how to get started, and believe me, you'll love them.
Before you begin, make sure you've loaded the Forms toolbar (select View/Toolbars/Forms, or right-click the toolbar area and select Forms; you can dock the toolbar to one edge of the screen or use it as a floating palette). Don't confuse the Forms toolbar with the Control Toolbox -- the two look similar, but work differently.
Create a tool. Choose View/Toolbars/Forms to see the toolbar and click the Checkbox tool. Drag a checkbox into cell B2. Right-click it and choose Format Control, then the Control tab. Set its Value to Unchecked, and in the "Cell link:" box, type D2 and click OK. Now change the label from Check Box 1 to Taxable. Click outside the checkbox, then click it a few times to enable and disable it -- watch the value in cell D2 change from True to False.
Use the tool. Once you have a checkbox which gives you a value, you can use it in your calculations. Into cell A2 type the value 2000, and into cell C2 type =IF(D2,A2*0.075,0). Now try the checkbox again: Combined with the IF function, it now calculates the tax on the amount in cell A2, if the amount is taxable.
Option buttons. While checkboxes allow you to toggle either/or choices, option buttons work a little differently. They let you choose among mutually exclusive options -- if one is enabled, the others in its group are automatically disabled. Begin by clicking the Group Box tool in the Forms toolbar and drawing a Group Box from B5 to C13. Rename it Shipping.
Now click the Option Button tool and put three option buttons inside the box, called Next Day Air, 2-3 Day Air, and 5-7 Day Ground. Right-click one of the Option buttons and choose Format Control and the Control tab; set it to Checked; set the "Cell link:" to D5; and choose OK. Now click each option button in turn, and notice that the value in cell D5 changes among 1, 2, and 3.
Choose the option. The CHOOSE function allows you to specify the value returned by each of the option buttons. Into cell E5, type the formula =CHOOSE(D5,50,35,25) and watch its value alter according to which option button is selected.
Go for a spin. Open a new sheet, click the Spinner tool, and drag a spinner into the left side of cells B2 to B3. Right-click the spinner and choose Format Control and the Control tab. Leave all the settings as they are except "Incremental change:" (set this to 100) and add D2 as the cell link. Choose OK and click the spinner's up and down arrows -- the value in cell D2 rises and falls in increments of 100 within the default range (0 to 30,000).
Overcome your limitations. Spinners and the Scroll Bar tool are limited to returning integers in the range 0 to 30,000. While this may seem to place a severe limit on their usefulness, you've probably already learned from other functions and tools that it's easy to take what the tool gives you (an integer, True/False, or whatever) and use a formula to convert the result into something meaningful. It's the same with Spinners and Scroll Bars, only in this situation, you'll need some calculations to convert the returned values into useful data.
So let's do the math: Add another spinner over cells B5 and B6, right-click it, choose Format Control, and give it a minimum value of 0, maximum value of 400, incremental change of 1, and cell link C5. Into cell D5, type the formula =(C5-200)/2 and test the spinner. The formula converts the spinner value into a number in the range -100 to 100 in increments of 0.5.
Managing percentages. There's no one way to create a formula to convert a spinner value; as long as what you write yields the values you need, that's all that matters. As another example, here's how to create a spinner that will give you a range of interest rates from 2.5 percent to 10 percent in quarter-percent increments: Put a spinner over cells B8 and B9 with a Format Control minimum value of 250, maximum value of 1000, incremental change of 25, and cell link C8. In cell D8, enter the formula =C8/10000, and right-click to format the cell as a percentage with two decimal places.
Scroll bars. Scroll bars work similarly to spinners, but include a "Page change:" value that gives you an additional increment tool, which you can use to make large changes where appropriate (say, rising or falling in increments of 1 when you click the arrows at either end of the bar, but increments of 10 if you click within the bar on either side of the scroll marker). In addition, scroll bars can be either horizontal or vertical in orientation. In all other respects, they work as spinners do.
Hide the evidence. If you limit your "Cell link:" values to a single column of a worksheet, you can hide that column when you're done. This makes the worksheet neater -- all you see are the final results, not the intermediate values the Forms tools provide.
