Article ID: 166932
Article Last Modified on 10/21/2000
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q166932
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:
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: kbhowto kbprogramming kbui kbprb KB166932
