How to use the forms controls on a worksheet in Excel

Microsoft Excel provides several controls for dialog sheets. You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list.

By adding a control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the control. You can use that numeric value in conjunction with the INDEX function to select different items from the list.

The following procedures demonstrate the use of drop-down boxes, list boxes, spinners, and scroll bars. The examples use the same list, cell link, and Index function.

To use the forms controls in Microsoft Office Excel 2007, you must enable the Developer tab. To do this, follow this steps:

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.

How to set up the list, the cell link, and the index

1. In a new worksheet, type the following items in the range H1:H20:

      H1 : Roller Skates
      H2 : VCR
      H3 : Desk
      H4 : Mug
      H5 : Car
      H6 : Washing Machine
      H7 : Rocket Launcher
      H8 : Bike
      H9 : Phone
      H10: Candle
      H11: Candy
      H12: Speakers
      H13: Dress
      H14: Blanket
      H15: Dryer
      H16: Guitar
      H17: Dryer
      H18: Tool Set
      H19: VCR
      H20: Hard Disk

2. In cell A1, type the following formula:=INDEX(H1:H20,G1,0)

List box example

1. To add a list box in Microsoft Office Excel 2003 and in earlier versions of Excel, click the List Box button on the Forms toolbar. Then, create a list box that covers cells B2:E10.If the Forms toolbar is not visible, point to Toolbars on the View menu, and then click Forms.

To add a list box in Excel 2007, click the Developer tab, click Insert, and then click List Box in the Form Controls section.

2. Right-click the list box, and then click Format Control. Type the following information, and then click OK.

a. To specify the range for the list, type H1:H20 in the Input range box.
b. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.Note The INDEX() formula uses the value in G1 to return the proper list item.
c. Under Selection type, make sure that the Single option is selected. Click OK.Note The Multi and Extend options are only useful when you are using a Microsoft Visual Basic for Applications procedure to return the values of the list. Note also that the 3-D shading check box adds a three-dimensional look to the list box.

The list box should display the list of items. To use the list box, click any cell so that the list box is not selected. If you click an item in the list, cell G1 is updated to a number that indicates the position of the item selected in the list. The INDEX formula in cell A1 uses this number to display the item’s name.

Combo box example

1. To add a combo box in Excel 2003 and in earlier versions of Excel, click the Combo Box button on the Forms toolbar.To add a combo box in Excel 2007, click the Developer tab, click Insert, and then click Combo Box in the Form Controls section.
2. Create an object that covers cells B2:E2.
3. Right-click the combo box, and then click Format Control. Enter the following information, and then click OK:

a. To specify the range for the list, type H1:H20 in the Input range box.
b. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.Note The INDEX formula uses the value in G1 to return the proper list item.
c. In the Drop down lines box, type 10. This entry determines how many items will be displayed before it is necessary to use a scroll bar to view the other items.Note The 3-D shading check box is optional; it adds a three-dimensional look to the drop-down or combo box.

The drop-down box or combo box should display the list of items. To use the drop-down box or combo box, click any cell so that the object is not selected. When you click an item in the drop-down box or combo box, cell G1 is updated to a number indicating the position in the list of the item selected. The INDEX formula in cell A1 uses this number to display the item’s name.

Spinner example

1. To add a spinner in Excel 2003 and in earlier versions of Excel, click the Spinner button on the Forms toolbar, and then create a spinner that covers cells B2:B3. Size the spinner to be about one-fourth of the width of the column.To add a spinner in Excel 2007, click the Developer tab, click Insert, and then click Spin Button in the Form Controls section.
2. Right-click the spinner, and then click Format Control. Enter the following information, and then click OK:

a. In the Current value box, type 1.This value initializes the spinner so the INDEX formula will point to the first item in the list.
b. In the Minimum value box, type 1.This value restricts the top of the spinner to the first item in the list.
c. In the Maximum value box, type 20.This number specifies the maximum number of entries in the list.
d. In the Incremental change box, type 1.This value controls how much the spinner control increments the current value.
e. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.

Click any cell so that the spinner is not selected. When you click the up control or down control on the spinner, cell G1 is updated to a number indicating the current value of the spinner plus or minus the incremental change of the spinner. This number then updates the INDEX formula in cell A1 to show the next or previous item.

The spinner value will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control.

Scroll bar example

1. To add a scroll bar in Excel 2003 and in earlier versions of Excel, click the Scroll Bar button on the Forms toolbar, and then create a scroll bar that covers cells B2:B6 in height and is about one-fourth of the width of the column.To add a scroll bar in Excel 2007, click the Developer tab, click Insert, and then click Scroll Bar in the Form Controls section.
2. Right-click the scroll bar, and then click Format Control. Type the following information, and click OK:

a. In the Current value box, type 1.This initializes the scroll bar so the INDEX formula will point to the first item in the list.
b. In the Minimum value box, type 1.This value restricts the top of the scroll bar to the first item in the list.
c. In the Maximum value box, type 20. This number specifies the maximum number of entries in the list.
d. In the Incremental change box, type 1.This value controls how many numbers the scroll bar control increments the current value.
e. In the Page change box, type 5. This entry controls how much the current value will be incremented if you click inside the scroll bar on either side of the scroll box).
f. To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell link box.Note The 3-D shading check box is optional; it adds a three-dimensional look to the scroll bar.

Click any cell so that the scroll bar is not selected. When you click the up or down control on the scroll bar, cell G1 is updated to a number that indicates the current value of the scroll bar plus or minus the incremental change of the scroll bar. This number is used in the INDEX formula in cell A1 to show the item next or previous to the current item.

You can also drag the scroll box to change the value or click in the scroll bar on either side of the scroll box to increment it by 5 (the Page change value). The scroll bar will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control.


APPLIES TO
Microsoft Office Excel 2007
Microsoft Excel 2004 for Mac
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2001 for Mac
Microsoft Excel 2000 Standard Edition
Microsoft Excel 98 for Macintosh
Microsoft Excel 97 Standard Edition

——————————————–

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

 


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Leave a Reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image