WorkbookBeforClose -> Cancel = True still closes excel

[Wallyken]

Hi,

I'm working with Excel 2003 SP3.
I have written some code in the Workbook_Before close event.
At the end of this event, I have written a messagebox where you can respond
on with yes or no, to keep the workbook open.
When I click the No button, I have written
Cancel=True
But excel keeps on closing my workbook.
I started debugging my code. It executes the Cancel=True statement, but does
not keep it open.
On other machines with the same version of excel, I do not have problems
like this.
I think it is a local setting that is different, but which one?
Can someone help me, plz.

Thx

 

[JP]

Hi,
Are you placing the code in the ThisWorkbook module?

Did you include an "Exit Sub" line after "Cancel = True"?

Try this code:

Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _
Cancel as Boolean)
a = MsgBox("Do you really want to close the workbook?", _
vbYesNo)
If a = vbNo Then Cancel = True
End Sub


HTH,
JP

 

[Wallyken]

Hi JP,

I've tried your code. It's placed in ThisWorkbook module.
But it is still closing excel when I click 'No'.
On another machine, excel don't get closed.
That's why I think it's a local setting (maybe somewhere in the registry).

thx,
Wallyken

 

[JP]

Sorry I'm stuck, hopefully others can come along and make other
suggestions.


--JP

 

[Peter T]

App_WorkbookBeforeClose ?

That looks like it's taken from some class (not the thisworkbook module)
that traps application level events, which in turn would need to be
instanciated with App set as a reference to the application.

Wallyken, try renaming JP's routine "Workbook_BeforeClose" placed in the
ThisWorkbook module and it should work as expected. Better still, select
'Workbook' from the top middle dropdown then the appropriate routine from
the top right dropdown and fill in the code.

Regards,
Peter T

 

[JP]

My mistake for cut-n-paste from microsoft.com without checking.

JP

 

[Jim Thomlinson]

Based on your description your Events are not firing on the one machine. Try
running this little procedure...

sub test
application.enableevents = true
end sub

Note that Application.Enableevents is a persistent setting. Once it is set
it remains in effect until code is used to change it back. Rebooting has no
effect. At some point that setting was set to false and it has probably been
false ever since. If you have the cube analysis addin it is a potential
culpret as it seems to toggle this setting without returning back to its
correct position. More than likely though it was just some poorly written
code or code that ended prematurely...

 

[Wallyken]

Hi all,

I took the Workbook_BeforeClose event in ThisWorkbook.

The events are enabled. If the events are not enabled, I even won't come in
my BeforeClose procedure.
My code is (the debug.print return value is True):
Private Sub Workbook_BeforeClose (Cancel as Boolean)
If MsgBox("Do you really want to close the workbook?", vbYesNo) = vbNo
Then
debug.Print Application.EnableEvents
Cancel = True
End If
End Sub

 

[Jim Thomlinson]

Do you get the message box coming up? What happens if you step through the
code?

 

[Wallyken]

I get the messagebox appearing.
When I click the No button and I step through my code, it executes the
Cancel=True code but doesn't do anything with it (excel closes anyway). I
have no error when stepping over this code.