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).
MORE INFORMATION
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.
- Start the Visual Basic Editor (press ALT+F11).
- Press CTRL+R to activate the Project Explorer Window.
- In the Project Explorer window, click to select "VBAProject (PERSONAL.XLS)."
- On the Insert menu, click Class Module to insert a class module.
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
- In the Project Explorer window of the Visual Basic Editor, double-click ThisWorkbook in the current project.
Type the following code into the ThisWorkbook module sheet:
Dim X As New Class1 Private Sub Workbook_Open() Set X.App = Application End Sub
- On the Insert menu, click Module to insert a Visual Basic module sheet.
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
- On the File menu, click "Close & Return to Microsoft Excel".
- 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
- Start Word.
- 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.
- Click the Word document to activate Word.
- 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.
- Click the Word document to activate Word.
- 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
- Right-click the first embedded Excel object, point to Worksheet Object on the shortcut menu, and then click Open.
- Activate Word.
- Right-click the second embedded Excel object, point to Worksheet Object on the shortcut menu, and then click Open.
- 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
- Activate the "Worksheet in BeforeCloseTest.doc" workbook.
- Type test into cell A1.
- 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