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

ID: Q166932

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

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

The list box does not display any contents, even though it is linked to a range of cells on a worksheet in the same workbook.
The list box appears to be linked to the correct range of cells, however the cells are in another workbook. As a result, the list box displays the incorrect contents.

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:

The list box is linked to a range of cells in a closed workbook or in an open workbook that has a window that is hidden.
-and-
The RowSource property of the list box refers to the worksheet name and a range but does not refer to the workbook name, for example:
```
      Sheet1!A1:A7
```

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