Knowledge Base

Scrolling Through List Box on Worksheet Runs Attached Macro

Article ID: 133046

Article Last Modified on 10/11/2006

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition
Microsoft Excel 98 for Macintosh

This article was previously published under Q133046

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.

WORKAROUND

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

Additional query words: 5.00a 5.00c 8.00 97 XL97 listbox dropdown Mac XL

Keywords: kbprb kbprogramming KB133046