Add a list box or combo box to a worksheet

You use a list box or a combo box, which are similar but slightly different controls, to let users make multiple choices of items or to enter their own value in a list. Typical examples of items in these types of lists are employee names, exchange rates, and product items.

What do you want to do?



Learn about list boxes and combo boxes

List box    Displays a list of one or more items of text from which a user can choose.

List box (Form control)

Example of a Form list box control

List box (ActiveX control)

Example of an ActiveX list box control


Combo box    Combines a text box with a list box to create a drop-down list box. A combo box is more compact than a list box, but it requires the user to click the down arrow to display the list of items. Use a combo box to enable a user either to type an entry or to choose only one item from a list. The control displays the current value in the text box, regardless of how that value was entered.

Combo box (Form control)

Example of a Form combo box control

Combo box (ActiveX control)

Example of an ActiveX combo box control


Top of Page Top of Page

Add a list box (Form control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click List box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the list box to appear.
  2. On the Developer tab, in the Controls group, click Properties Edit code button.

 Tip   You can also right-click the control, and then click Format Control.

To specify the control properties, do the following:

  1. In the Input range box, enter a cell reference to a range that contains the values to display in the list box.
  2. In the Cell link box, enter a cell reference that contains the list box selection.

The linked cell returns the number of the selected item in the list box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on.

Use this number in a formula to return the actual item from the input range.

For example, a dessert preference form has a list box that is linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the list box.

=INDEX(D1:D5,C1)
  1. Under Selection type, specify how items can be selected in the list box by doing one of the following:
    • To create a single-selection list box, click Single.
    • To create a multiple-selection list box, click Multi.
    • To create an extended-selection list box, click Extend.

 Note   If you set the selection type to Multi or Extend, the cell that is specified in the Cell link box returns a value of 0 and is ignored. The Multi and Extend selection types require the use of Microsoft Visual Basic for Applications (VBA) code. In these cases, consider using the ActiveX list box control.

Top of Page Top of Page

Add a list box (ActiveX control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click List Box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the list box to appear.
  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  3. To specify the control properties, on the Developer tab, in the Controls group, click Properties Edit code button.

 Tip   You can also right-click the control, and then click Properties.

The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify Use this property
General:  
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events. Enabled (Form)
Whether the control can be edited. Locked (Form)
The name of the control. Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). Placement (Excel)
Whether the control can be printed. PrintObject (Excel)
Whether the control is visible or hidden. Visible (Form)
Text:  
Font attributes (bold, italic, size, strikethrough, underline, and weight). Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
The default run time mode of the Input Method Editor (IME). IMEMode (Form)
Whether the size of the control adjusts to display full or partial lines of text. IntegralHeight (Form)
Whether multiple selections of items are permitted. MultiSelect (Form)
The text in the control. Text (Form)
How text is aligned in the control (left, center, or right). TextAlign (Form)
Data and Binding:  
The range that is linked to the control's value. LinkedCell (Excel)
The content or state of the control. Value (Form)
Size and Position:  
The height or width in points. Height, Width (Form)
The distance between the control and the left or top edge of the worksheet. Left, Top (Form)
Formatting:  
The background color. BackColor (Form)
The color of the border. BorderColor (Form)
The type of border (none or single-line). BorderStyle (Form)
The foreground color. ForeColor (Form)
Whether the control has a shadow. Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump). SpecialEffect (Form)
Keyboard and Mouse:  
A custom mouse icon. MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). MousePointer (Form)
Specific to List Box:  
The source of data for multiple columns. BoundColumn (Form)
The number of columns to display. ColumnCount (Form)
A single row as a column heading. ColumnHeads (Form)
The width of each column. ColumnWidths (Form)
The range that is used to populate the list. ListFillRange (Excel)
The list style (plain, with option buttons, or with check boxes). ListStyle (Form)
How the control searches its list while the user types (first letter, complete entry, or none) MatchEntry (Form)
The column to store in the Text property when the user selects a row. TextColumn (Form)
The item that appears in the topmost position in the list. TopIndex (Form)

 Notes 

  • To create a list box with multiple selection or extended-selection enabled, use the MultiSelect property. In this case, the LinkedCell property returns a #N/A value. You must use VBA code to process the multiple selections.
  • To create a two-column list box with column headers, set ColumnCount to 2, ColumnHeads to True, ColumnWidths to the width that you want for each column (for example, 72pt;72pt), ListFillRange to the range that is used to populate the list (for example, B2:C6), BoundColumn to either 1 or 2 to indicate which column value to save, and LinkedCell to a cell address that contains the selected value. By default, the column label is used as the column header (for example, Column B and Column C). To use your own column headers, place them immediately above the first value specified in ListFillRange (for example, B1 and C1) before you close the Properties dialog box. Finally, resize the list box to display both columns.
  • To create a list box that displays one value in the list box but saves another value in the linked cell, create a two-column list box, and then hide one of the columns by setting its ColumnWidths value to 0. For example, you can set up a two-column list box that contains the names of holidays in one column and dates associated with the holidays in a second column. To present the holiday names to users, specify the first column as the TextColumn. To store the dates of the holidays, specify the second column as the BoundColumn. To hide the dates of the holidays, set the ColumnWidths property of the second column to 0.

Top of Page Top of Page

Add a combo box (Form control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Combo box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the combo box to appear.

The drop-down arrow is displayed with the text box collapsed.

  1. To display the text box, drag the left-center sizing handle to the right.
  2. On the Developer tab, in the Controls group, click Properties Button image.

 Tip   You can also right-click the control, and then click Format Control.

To specify the control properties, do the following:

  1. In the Input range box, enter a cell reference to a range that contains the values to display in the drop-down list of the combo box.
  2. In the Cell link box, enter a cell reference that contains the selection in the drop-down list of the combo box.

The linked cell returns the number of the selected item in the drop-down list of the combo box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on.

Use this number in a formula to return the actual item from the input range. For example, a dessert preference form has a combo box linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the combo box.

=INDEX(D1:D5,C1)

 Note   If you want to create a combo box that enables the user to edit the text in the text box, consider using the ActiveX Combo Box control.

  1. In the Drop-down lines box, enter the number of lines to display in the drop-down list of the combo box. If the value is:
    • 0, it is ignored and treated as 1.
    • Less than the number of items in the range specified in the Input range box, a scroll bar is displayed.
    • Equal to or greater than the number of items in the range specified in the Input range box, no scroll bar is displayed.

Top of Page Top of Page

Add a combo box (ActiveX control)

  1. If the Developer tab is not available, display it.

ShowDisplay the Developer tab

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Combo Box Button image.

Controls group

  1. Click the worksheet location where you want the upper-left corner of the combo box to appear.
  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode Button image.
  3. To specify the control properties, on the Developer tab, in the Controls group, click Properties Edit code button.

 Tip   You can also right-click the control, and then click Properties.

The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify Use this property
General:  
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events. Enabled (Form)
Whether the control can be edited. Locked (Form)
The name of the control. Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). Placement (Excel)
Whether the control can be printed. PrintObject (Excel)
Whether the control is visible or hidden. Visible (Form)
Text:  
Whether a word or a character is the basic unit used to extend a selection. AutoWordSelect (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight). Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
Whether selected text remains highlighted when the control does not have the focus. HideSelection (Form)
The default run time mode of the Input Method Editor (IME). IMEMode (Form)
The maximum number of characters a user can enter. MaxLength (Form)
Whether the user can select a line of text by clicking to the left of the text. SelectionMargin (Form)
The text in the control. Text (Form)
How text is aligned in the control (left, center, or right). TextAlign (Form)
Data and binding:  
The range that is linked to the control's value. LinkedCell (Excel)
The content or state of the control. Value (Form)
Size and position:  
Whether the size of the control automatically adjusts to display all the contents. AutoSize (Form)
The height or width in points. Height, Width (Form)
The distance between the control and the left or top edge of the worksheet. Left, Top (Form)
Formatting:  
The background color. BackColor (Form)
The background style (transparent or opaque). BackStyle (Form)
The color of the border. BorderColor (Form)
The type of border (none or single-line). BorderStyle (Form)
The foreground color. ForeColor (Form)
Whether the control has a shadow. Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump). SpecialEffect (Form)
Keyboard and mouse:  
Whether an automatic tab action occurs after a user has entered the maximum number of characters for the control. AutoTab (Form)
Whether drag-and-drop is enabled. DragBehavior (Form)
The selection behavior when entering the control (select all or do not change). EnterFieldBehavior (Form)
A custom mouse icon. MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). MousePointer (Form)
Specific to combo box:  
The source of data for multiple columns. BoundColumn (Form)
The number of columns to be displayed. ColumnCount (Form)
A single row as a column heading. ColumnHeads (Form)
The width of each column. ColumnWidths (Form)
The symbol that is displayed on the drop button (down arrow, plain, ellipsis, or underscore). DropButtonStyle (Form)
The range that is used to populate the list. ListFillRange (Excel)
The maximum number of rows to display in the list. ListRows (Form)
The list style (plain, with option buttons, or with check boxes). ListStyle (Form)
The width of the list. ListWidth (Form)
How the control searches its list while the user types (first letter, complete entry, or none) MatchEntry (Form)
Whether a value entered as text must match an entry in the existing list. MatchRequired (Form)
When to show the drop button (never, with focus, or always). ShowDropButtonWhen (Form)
How the user chooses or sets the value (drop-down combo or drop-down list). Style (Form)
The column to store in the Text property when the user selects a row. TextColumn (Form)
The item that appears in the topmost position in the list. TopIndex (Form)

 Notes 

  • To create a two-column combo box with column headers, set ColumnCount to 2, ColumnHeads to True, ColumnWidths to the width that you want for each column (for example, 72pt;72pt), ListFillRange to the range that is used to populate the list (for example, B1:C6), BoundColumn to either 1 or 2 to indicate which column value to save, TextColumn to the column of data that you want displayed in the text box section of the combo box (which can be the same as or different from the BoundColumn value), and LinkedCell to a cell address that will contain the selected value. By default, the column label is used as the column header (for example, Column B and Column C). To use your own column headers, place them immediately above the first value specified in ListFillRange (for example, B1 and C1) before you close the Properties dialog box. Finally, resize the combo box to display both columns.
  • To create a combo box that displays one value in the combo box but saves another in the linked cell, create a two-column combo box, and then hide one of the columns by setting its ColumnWidths value to 0. For example, you can set up a two-column combo box that contains the names of holidays in one column and associated dates for the holidays in a second column. To present the holiday names to users, specify the first column as the TextColumn. To store the dates of the holidays, specify the second column as the BoundColumn. To hide the dates of the holidays, set the ColumnWidths property of the second column to 0.
  • To create a combo box that does not enable the user to enter new values, set Style to 2. To create a combo box that enables a user to enter new values that are not found in the list, set Style to 1, which is the default. In this case, you must write VBA code if you want to dynamically update the list values.

Top of Page Top of Page

 
 
Applies to:
Excel 2007