Use the List Box Control for Your Excel Dashboards

You can use List Box controls to allow your Excel dashboard and report users to select from a list of predefined choices. When an item from the List Box control is selected, some action is taken with that selection. To add a list box to your worksheet, follow these steps:

  1. Select the Insert drop-down list under the Developer tab.

  2. Select the List Box Form control.

  3. Click the location in your spreadsheet where you want to place your list box.

  4. After you drop the control onto your worksheet, right-click the control and select Format Control.

  5. Click the Control tab to see the configuration options shown in this figure.

    image0.jpg
  6. In the Input Range setting, identify the range that holds the predefined items you want to present as choices in the list box.

    As you can see in the figure, this list box is filled with region selections.

  7. In the Cell Link box, enter the cell where you want the list box to output its value.

    By default, a List Box control outputs the index number of the selected item. This means that if the second item on the list is selected, the number 2 will be output. If the fifth item on the list is selected, the number 5 will be output. Notice in the figure that this particular control outputs to cell P2.

    The Selection Type setting allows users to choose more than one selection in the list box. The choices here are Single, Multi, and Extend. Always leave this setting on Single because Multi and Extend work only in the VBA environment.

  8. (Optional) You can select the 3-D Shading check box if you want the control to have a three-dimensional appearance.

  9. Click OK to apply your changes.

blog comments powered by Disqus
Advertisement

Inside Dummies.com