XL2000: Scrolling Through a List Box on a Worksheet Runs the Attached Macro (213819)

SYMPTOMS

In Microsoft Excel, when you place a ListBox control on a worksheet and attach a macro to the list box, the attached macro is run every time that you click the scroll bar.

This behavior may not be what you want or expect. For example, you may want the macro to run only when a user selects a new item in the list box.

WORKAROUNDS

To work around this behavior, use either of the following methods.

Method 1: Adjust the Size of the List Box

You can adjust the size of the list box on the worksheet to display all of the items in the list at one time. Therefore, the user does not have to use the scroll bar.

Method 2: Use a Drop-Down Box

You can use a drop-down box rather than a list box. The drop-down box displays the last selected item in the list, but you can click the drop-down arrow and scroll through the list with the drop-down scroll bar, and the attached macro does not run until you select a new item in the list.

MORE INFORMATION

In Excel, you can place controls, such as list boxes and drop-down boxes, on a worksheet. You can also attach macros to these controls; the macros should run when you select a new item in the list that is contained in the list box or drop-down box.

REFERENCES

For more information about ListBox controls, click Microsoft Visual Basic Help on the Help menu, type listbox control in the Office Assistant or the Answer Wizard, and then click Search to view the topic.