XL: Scrolling Through List Box on Worksheet Runs Attached Macro

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you place a list box control on a worksheet and attach a macro to the list box, the attached macro is run every time you click the scroll bar.

This behavior may not be what you expect. For example, you may want the macro to be run only when a user selects a new item from 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. This way, you will 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 will display the last chosen item from the list, but you can click on the drop- down arrow and scroll through the list with the drop-down scroll bar and the attached macro will not run until you select a new item from the list.

MORE INFORMATION

In Microsoft 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 be run when you choose a new item from the list that is contained in the list box or drop-down box.

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 11, "Controls and Dialog Boxes"

For more information about List Box controls, click the Search button in Help and type:

   List box button