XL97: WorkBookBeforeClose Event Fires Unexpectedly

ID: Q176251

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

When you use a Visual Basic for Applications macro to update a formula in a Microsoft Excel worksheet, the WorkbookBeforeClose event may fire.

CAUSE

This problem may occur when the following conditions are true:

The macro is updating two or more Excel workbooks that have been inserted into another program (such as Microsoft Word).
```
    -and-
```
The macro updates a formula that represents an OLE link between the Excel workbooks.
```
    -and-
```
You have created an Application-level event handler for the WorkbookBeforeClose event.

RESOLUTION

To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q151261
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-2

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

MORE INFORMATION

Example of the Problem

The following steps illustrate how to reproduce the problem.

Create the WorkbookBeforeClose Event Handler

 1. Start Excel.

 2. If you do not have a Personal Macro Workbook (Personal.xls), the
    following steps illustrate how to create one. Proceed to Step 3 if you
    already have a Personal Macro Workbook.

     a. On the Tools menu, point to Macro, and then click Record New Macro.

     b. In the Store Macro In box, click Personal Macro Workbook, and then
        click OK.

     c. On the Tools menu, point to Macro, and then click Stop Recording.

 3. Start the Visual Basic Editor (press ALT+F11).

 4. Press CTRL+R to activate the Project Explorer Window.

 5. In the Project Explorer window, click to select "VBAProject
    (PERSONAL.XLS)."

 6. On the Insert menu, click Class Module to insert a class module.

 7. Type the following code into the class module:

     Public WithEvents App As Application

     Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, _
     Cancel As Boolean)

        MsgBox "App_WorkbookBeforeClose"

     End Sub

 8. In the Project Explorer window of the Visual Basic Editor, double-click
    ThisWorkbook in the current project.

 9. Type the following code into the ThisWorkbook module sheet:

     Dim X As New Class1

     Private Sub Workbook_Open()

        Set X.App = Application

     End Sub

10. On the Insert menu, click Module to insert a Visual Basic module sheet.

11. Type the following code into the module sheet:

     Sub Test_BeforeClose()

        Dim xFormula As String, i As Integer, j As Integer

        Windows("Worksheet in BeforeCloseTest.Doc").Activate

        i = Workbooks("Worksheet in BeforeCloseTest.Doc 2") _
           .Worksheets("Sheet1").Range("A100").End(xlUp).Row + 1

        Range("A1").Name = "RefCopy" & I

        Range("A1").Copy

        Windows("Worksheet in BeforeCloseTest.Doc 2").Activate

        Range("A" & i).Select

        ActiveSheet.Paste Link:=True

        xFormula = ActiveCell.Formula

           For j = Len(xFormula) To 1 Step -1

              If Mid(xFormula, j, 1) = "!" Then Exit For

           Next j

        ActiveCell.Formula = Left(xFormula, j) & "RefCopy" & i & "'"

     End Sub

12. On the File menu, click "Close & Return to Microsoft Excel".

13. On the File menu, click Exit. Click Yes when you are prompted to save

    the changes in the Personal Macro Workbook.

Inserting Excel Objects into a Word Document

1. Start Word.

2. On the Insert menu, click Object. Click Microsoft Excel Worksheet in the

   Object Type box, and then click OK.

   An Excel worksheet object is inserted into your document.

3. Click the Word document to activate Word.

4. On the Insert menu, click Object. Click Microsoft Excel Worksheet in the

   Object Type box, and then click OK.

   An Excel worksheet object is inserted into your document.

5. Click the Word document to activate Word.

6. On the File menu, click Save As. Type "BeforeCloseTest.doc" (without

   the quotation marks) into the File Name box, and then click OK.

Running the Macro that Illustrates the Problem

1. Right-click the first embedded Excel object, point to Worksheet

   Object on the shortcut menu, and then click Open.

2. Activate Word.

3. Right-click the second embedded Excel object, point to Worksheet

   Object on the shortcut menu, and then click Open.

4. On the Tools menu, point to macro, and then click Macros. Click

   Personal.xls!Test_BeforeClose, and then click Run.

   An OLE link is created between cell A1 of the "Worksheet in
   BeforeCloseTest.doc" workbook and cell A2 of the "Worksheet in
   BeforeCloseTest.doc 2" workbook

5. Activate the "Worksheet in BeforeCloseTest.doc" workbook.

6. Type "test" (without the quotation marks) into cell A1.

7. On the Tools menu, point to macro, and then click Macros. Click

   Personal.xls!Test_BeforeClose, and then click Run.

A message box is displayed that indicates that the WorkbookBeforeClose event has fired. The WorkbookBeforeClose event will fire each time you run the Test_BeforeClose procedure.

Additional query words: XL97

Keywords          : kbprg kbdta xlformat 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug

Last Reviewed: November 2, 1998