XL97: List Box Doesn't Work Correctly When Workbook Is Hidden

ID: Q166932

The information in this article applies to:

SYMPTOMS

When you display a UserForm that contains a list box in Microsoft Excel 97, the following problems may occur:

Also, when you change the RowSource property of a list box in a UserForm, you may receive the following error message:

   Could not set the RowSource property. Invalid property value.

CAUSE

These problems occur if the following conditions are true:

RESOLUTION

To resolve this problem, change the RowSource property for the list box to include the workbook name, the worksheet name, and the range that is used by the list box. You can change the RowSource property for a list box by selecting the list box and then double-clicking RowSource in the Properties window of the Visual Basic Editor.

For example, if the RowSource property for a list box is as follows

   Sheet1!A1:A7

change the RowSource property to the following

   [<Book1>]Sheet1!A1:A7

where <Book1> is the name of the workbook that contains the range of cells.

NOTE: The workbook to which the list box is linked must be open.

If you rename the workbook by changing its name in the Save As dialog box, you must change the RowSource property of each of the list boxes. Otherwise, the list boxes continue to refer to the original workbook name.

To prevent this problem from occurring, do not hide the workbook that contains the range of cells to which the list box is linked. To unhide a workbook, click Unhide on the Window menu, click the workbook you want to unhide, and then click OK.

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

If the RowSource property of a list box refers to the following range

   Sheet1!A1:A7

the list box refers to the specified range in the workbook only if the workbook is open and its window is not hidden. Otherwise, the list box uses the range in Sheet1!A1:A7 that is in another open workbook. In this case, the list box will contain incorrect data. If no other visible workbook is open, the list box refers to an invalid range and appears empty.

Additional query words: XL97 forms3

Keywords          : kbprg kbui kbdta KbVBA kbhowto 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: December 9, 1998