Using form controls on a worksheet

Today’s author is Rob van Gelder, previously quite active in the online Excel scene (Newsgroups, Excel forums, his own but currently inactive website) and who just upgraded to Excel 2007 because he still likes to dabble. The following power tip shows that he’s still having fun with form controls.

My goal is to use form controls on my sheet.

· One up/down button to change the Edit Year

· One slider to change the Quantity

· One slider to change the Price

I don't need a slider for Total Value, because it's a formula (Quantity * Price).

I want the slider to change only the year that I'm editing. So if the Edit Year is 2010, then the sliders should only change the Quantity and Price values for the 2010 column.

I hope you get the idea...

First, I need to enable Form Controls. In Excel 2003 and earlier, these were available from the Forms toolbar.

From Excel 2007, you need to specifically enable the Developer Ribbon.

1. Click the Microsoft Office Button, and then click Excel Options.
2. In the Popular tab, click to select the Show Developer tab in the Ribbon check box, and then click OK.

Now I want to drop an up/down button on my sheet (technically, it's called a Spin Button).

From the Developer tab on the Ribbon:

1. Click Insert in the Controls group, and then insert a Spin Button (from the Form Controls) on the sheet.
2. Right-click it, and then click Format Control.
3. Set the Cell link to the Edit Year cell $B$2.
4. Set Minimum and Maximum values to 2009 through to 2013.

Dynamic Ranges:

If you've worked with the Slider control before, you'll know it requires a "Cell Link".

First impression is that it's firmly linked to a single cell - it changes one cell and one cell only.

But! You can trick it into loosening its grip by pointing the Slider at a Dynamic Named Range, which in turn points at the cell location to change.

You edit Named Ranges from the Name Manager (Ctrl+F3).

What's a "Dynamic Named Range", you might ask?

It's a named range that uses a formula to reference a cell location instead of a straight $A$1 type reference.

So, for example, instead of a "Refers To" formula of =$A$5, I might use the formula

=OFFSET($A$1, $B$1, 0)

where B1 contains the value 4.

The Named Range for Quantity and Price could be:

=OFFSET($C$4, 0, MATCH($B$2, $D$2:$H$2, 0))

=OFFSET($D$4, 0, MATCH($B$2, $D$2:$H$2, 0))

But notice that there is opportunity to avoid some repetition.

In the end I've settled on 3 named ranges:

Year_Offset: =MATCH($B$2, $D$2:$H$2, 0)

Quantity: =OFFSET($C$4, 0, Year_Offset)

Price: =OFFSET($C$5, 0, Year_Offset)

Let's drop the Quantity slider on the sheet (technically, it's called a Scroll Bar).

From the Developer tab on the Ribbon:

1. Click Insert in the Controls group, and then insert a Scroll Bar (from the Form Controls) on the sheet.
2. Right-click it, and then click Format Control...
3. Set the Cell link to Quantity   (just type the word “Quantity”)
4. Set Minimum and Maximum values to 0 through to 1000

Then just under the Quantity slider, drop the Price slider on the sheet.

From the Developer tab on the Ribbon:

1. Click Insert in the Controls group, and then insert a Scroll Bar (from the Form Controls) on the sheet.
2. Right-click it, and and then click Format Control...
3. Set the Cell link to Price (just type the word “Price”)
4. Set Minimum and Maximum values to 0 through to 1000

For more information about how to use form controls, see Overview of forms, Form controls, and ActiveX controls on a worksheet.