Microsoft KB Archive/142135

= XL: How to Use the Forms Controls on a Worksheet =

PSS ID Number: 142135

Article Last Modified on 9/11/2002

-

The information in this article applies to:


 * Microsoft Excel for Windows 5.0
 * Microsoft Excel for Windows 5.0c
 * Microsoft Excel for the Macintosh 5.0
 * Microsoft Excel for the Macintosh 5.0a
 * Microsoft Excel for Windows 95 7.0
 * Microsoft Excel 97 for Windows
 * Microsoft Excel 98 Macintosh Edition

-



This article was previously published under Q142135



For a Microsoft Excel 2002 version of this article, see 291073.

For a Microsoft Excel 2000 version of this article, see 214262.



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



MORE INFORMATION
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-downs, list boxes, spinners, and scroll bars. The examples use the same list, cell link, and Index function.

To Set Up the List, Cell Link, and Index
  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  In cell A1, type the following formula:

=INDEX(H1:H20,G1,0)



List Box Example
 On the Forms toolbar, click the List Box button and create a list box that covers cells B2:E10.

If the Forms toolbar is not visible in Excel 97 and Excel 98 Macintosh Edition, point to Toolbars on the View menu, and then click Forms. In earlier versions, click Toolbars on the View menu, select the Forms toolbar check box, and click OK. In Excel 97 and Excel 98 Macintosh Edition, click Control on the Format menu. In earlier versions, click Object on the Format menu. Click the Control tab, enter the following information, and click OK:  To specify the range for the list, type H1:H20 in the Input Range box.</li> 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.</li> Under Selection Type, make sure that the Single option is selected. Click OK.

NOTE: The Multi and Extended 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 3D Shading check box adds a three-dimensional look to the list box.</li></ol> </li></ol>

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. When you click an item in the list, cell G1 is updated to a number indicating the position of the item selected in the list. The INDEX formula in cell A1 uses this number to display the item's name.

Drop-Down Box Example
 In Excel 97 or Excel 98 Macintosh Edition, click the Combo Box button on the Forms toolbar. In earlier versions, click the Drop-Down button on the Forms toolbar.</li> Create an object that covers cells B2:E2.</li> In Excel 97 and Excel 98 Macintosh Edition, click Control on the Format menu. In earlier versions, click Object on the Format menu. Click the Control tab, enter the following information, and click OK:  To specify the range for the list, type H1:H20 in the Input Range box.</li> 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.</li> In the Drop-Down Lines box, type 10. Ignore this step if you are using Excel for the Macintosh. 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 3D Shading check box is optional; it adds a three-dimensional look to the drop-down or combo box.</li></ol> </li></ol>

The drop-down or combo box should display the list of items. To use the drop-down or combo box, click any cell so that the object is not selected. When you click an item in the drop-down 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
 On the Forms toolbar, click the Spinner button, and create a spinner that covers cells B2:B3. Size the spinner to be about one-fourth of the width of the column.</li> In Excel 97 and Excel 98 Macintosh Edition, click Control on the Format menu. In earlier versions, click Object on the Format menu. Click the Control tab, type the following information, and click OK  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.</li> <li>In the Minimum Value box, type 1. This value restricts the top of the spinner to the first item in the list.</li> <li>In the Maximum Value box, type 20. This number specifies the maximum number of entries in the list.</li> <li>In the Incremental Change box, type 1. This value controls how much the spinner control increments the current value.</li> <li>To put a number value in cell G1 (depending on which item is selected in the list), type G1 in the Cell Link box.</li></ol> </li></ol>

Click any cell so that the spinner is not selected. When you click the up 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
<ol> <li>On the Forms toolbar, click the Scroll Bar button and create a scroll bar that covers cells B2:B6 in height and is about one-fourth of the width of the column.</li> <li>In Excel 97 and Excel 98, click Control on the Format menu. In earlier versions, click Object on the Format menu. Click the Control tab, enter the following information, and click OK: <ol style="list-style-type: lower-alpha;"> <li>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.</li> <li>In the Minimum Value box, type 1. This value restricts the top of the scroll bar to the first item in the list.</li> <li>In the Maximum Value box, type 20. This number specifies the maximum number of entries in the list.</li> <li>In the Incremental Change box, type 1. This value controls how many numbers the scroll bar control increments the current value.</li> <li>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).</li> <li>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 3D Shading check box is optional; it adds a three-dimensional look to the scroll bar.</li></ol> </li></ol>

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 indicating 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.

<div class="references_section">

Microsoft Excel 97
For more information about how to add controls to a worksheet, click Contents and Index on the Help menu, click the Index tab in Excel Help, type the following text

forms toolbar

and then double-click the selected text to go to the "Add buttons, check boxes or other controls to a worksheet" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: xl97 8.00 dropdown

Keywords: kbhowto kbinfo kbualink97 KB142135

Technology: kbExcel500 kbExcel500aMac kbExcel500c kbExcel500Mac kbExcel95 kbExcel95Search kbExcel97Search kbExcel98 kbExcel98Search kbExcelMacsearch kbExcelSearch kbExcelWinSearch kbHWMAC kbOSMAC kbZNotKeyword3

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.