Making List Boxes for Excel Forms in Office 2011 for Mac

By Geetesh Bajaj, James Gordon

Use a list box when you’re making a form in Excel 2011 for Mac and have a long list of items from which a choice is to be made. The list box will report which item was selected with a number that shows how many items from the top of the list was chosen.

The first item is #1, second #2, and so on. A list box comes with a built-in scroll bar. The range of cells that is being used to populate the list box can be hidden or placed on a different worksheet.

To add a list box to your worksheet:

  1. While your form is unprotected, click the List Box control on the Developer tab of the Ribbon.

  2. Drag diagonally and then let go of the mouse.

    An empty list box appears and is selected on your worksheet.

  3. Right-click the new list box control and choose Format Control from the pop-up menu.

    The Format Control dialog displays.

  4. On the Control tab of the Format Control dialog, choose a range in a column that has the values you want to use to populate the control.

    Don’t include the header; include only cells that contain data. Blanks don’t look right in a control, so make sure your data is contiguous.

  5. On the Control tab of the Format Control dialog, set a cell link by clicking into the empty Cell Link field and then clicking a cell on any worksheet.

    The linked cell is indicated in the Cell Link field in the dialog. This cell is where the form user’s choice will display.

  6. On the Control tab of the Format Control dialog, choose a selection type.

    Of the three selection types, only Single takes advantage of the linked worksheet cell. Multi and Extend selection types allow users to choose more than one item in your list box, but you need VBA to determine what was selected.

  7. Deselect the Locked check box on the Protection tab of the Format Control dialog to unlock the linked cell.

    The control won’t work when you protect the worksheet unless you unlock the linked cell.

  8. Click OK to close the Format Control dialog.