The information in this article applies to:
- Microsoft Excel 97 for Windows
SYMPTOMS
In Microsoft Excel 97, code that is contained "behind" a worksheet or
workbook may not work correctly.
CAUSE
This will occur if both of the following conditions are true:
- The code in question is contained inside an automatically-running
subroutine, such as an Auto_Open or Auto_Close subroutine.
-and-
- The code is not contained in a Visual Basic module, but "behind" a
worksheet or the workbook (ThisWorkbook) itself.
WORKAROUND
In order for automatically-running subroutines to work correctly in
Microsoft Excel 97, they must be contained within a Visual Basic module. To
insert a new module:
- In the Project Explorer in the Visual Basic Editor, activate the
workbook that contains the code in question.
- On the Insert menu, click Module.
Once the new module is inserted, cut the code from its original location
and paste it into the Visual Basic module. Then, save the workbook.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article.
MORE INFORMATION
In Microsoft Excel 97, Visual Basic for Applications code can be stored in
three different locations: in a Visual Basic module, in a Visual Basic
class module, and "behind" worksheets and workbooks.
To edit code "behind" a worksheet or a workbook:
- Activate the Visual Basic Editor by pressing ALT + F11 on your
keyboard.
- In the Project Explorer window, you will see entries similar to the
following:
VBAProject (Book1)
Microsoft Excel Objects
Sheet1 (Sheet1)
Sheet2 (Sheet2)
Sheet3 (Sheet3)
ThisWorkbook
Modules
Module1
If you double-click one of the worksheet names or ThisWorkbook, you
can enter code in the code window. Such code not contained within a
Visual Basic module is considered to be "behind" the worksheet or
ThisWorkbook.
If an Auto_Open, Auto_Close, or other automatically-running subroutine is
stored "behind" a worksheet or ThisWorkbook, it may not function correctly
when you open or close your workbook, or when you perform an action that
should cause the subroutine to run.