Microsoft KB Archive/176251

= XL97: WorkBookBeforeClose Event Fires Unexpectedly =

Article ID: 176251

Article Last Modified on 9/22/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q176251





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:

151261 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).



Example of the Problem
The following steps illustrate how to reproduce the problem.

Create the WorkbookBeforeClose Event Handler
 Start Excel. 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.

 On the Tools menu, point to Macro, and then click Record New Macro. In the Store Macro In box, click Personal Macro Workbook, and then click OK. On the Tools menu, point to Macro, and then click Stop Recording.</li></ol> </li> Start the Visual Basic Editor (press ALT+F11).</li> Press CTRL+R to activate the Project Explorer Window.</li> In the Project Explorer window, click to select "VBAProject (PERSONAL.XLS)."</li> On the Insert menu, click Class Module to insert a class module.</li>  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 </li> In the Project Explorer window of the Visual Basic Editor, double-click ThisWorkbook in the current project.</li>  Type the following code into the ThisWorkbook module sheet: Dim X As New Class1 Private Sub Workbook_Open

Set X.App = Application

End Sub </li> On the Insert menu, click Module to insert a Visual Basic module sheet.</li>  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 </li> On the File menu, click "Close & Return to Microsoft Excel".</li> On the File menu, click Exit. Click Yes when you are prompted to save the changes in the Personal Macro Workbook.</li></ol>

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.
 * 1) Click the Word document to activate 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.
 * 1) Click the Word document to activate Word.
 * 2) 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
 * 1) Activate the "Worksheet in BeforeCloseTest.doc" workbook.
 * 2) Type test into cell A1.
 * 3) 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: kbbug kbqfe kbprogramming kbhotfixserver KB176251

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.