MrExcel Publishing
Your One Stop for Excel Tips & Solutions

BeforeClose Event does not run


Posted by Keith Hoar on February 09, 2000 12:26 PM

I have tried all combinations of the BeforeClose event: WorkBook_BeforeClose, App_WorkBook_BeforeClose, WorkBookBeforeClose, and BeforeClose. Nothing works.

Copied sample directly from MSDN. Set break point in WorkBook_BeforeClose sub but the debugger never stops after after "File -- Close" is clicked.

Three other users tried Workbook_BeforeClose in new workbooks on their PCs. they did not run either.

This is just too simple. What am I doing wrong.

Sample: (Does not run)

Sub WorkBook_BeforeClose(Cancel As Boolean)

Dim x As String
x = "XXX"

Cancel = True

End Sub


Posted by Ivan Moala on February 10, 2000 1:21 AM

Cancel = True


Hi Keith
I tried the above and it worked as expected ??
ie it assigned the "XXX" to variable x then canceled
the before close event, which without the
Cancel = True would prompt you with the
"Do you want to save changes you made to etc"
and then the 3 options.
What exactly are you trying to do with this??


Ivan

Posted by Keith Hoar on February 15, 2000 2:25 PM

I found that the BeforeClose code HAS to go in ThisWorkbook. It will NOT work in a module.
Go Figure.

Keith

Posted by Celia on February 15, 2000 9:20 PM

Keith
Yes that's right. That's the rule!
You cannot include any of the Excel 97 event procedures in normal code modules. They will not run.
They can only be run if created in their own relevant module. There are four "object" types :-
-Worksheet
-Chart
-Workbook
-Application
Each object type has its own event procedure module.
Also, the only code that should be in these modules is event procedure code relative to the particular object. Do not put your own macros or functions in them.
In Excel 97, you can still use the Excel 95 macros such as "Auto_Open" and "Auto_Close" in the normal modules.
Celia