XL97: WorkBookBeforeClose Event Fires Unexpectedly

• 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:

Q151261 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

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.
   
   
   1. On the Tools menu, point to Macro, and then click Record New Macro.
   
   
   
   2. In the Store Macro In box, click Personal Macro Workbook, and then click OK.
   
   
   
   3. 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 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 into cell A1.



7. On the Tools menu, point to macro, and then click Macros. Click Personal.xls!Test_BeforeClose, and then click Run.

Additional query words: XL97

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