Workbook_BeforeClose event - Cancel won't work

[gpmichal]

I mis-posted this in Microsoft.Public.Excel.Setup so I'm posting again here
being that this group seem more appropriate.

Please help...

I'm tyring to use the Workbook_BeforeClose event in an Excel 2007
ThisWorkbook object. When the code sets the Cancel parameter to "True", the
workbook isn't supposed to close. For testing purposes, I used the following
code on several computers:


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

On my home computer, and others at work, the code works fine (the workbook
won't close). On some work computers, including my own, the workbook closes.
This is very frustrating.

All computers are using the same operating system; Windows XP Professional
Version 2002 SP3 and the same software; Office 2007 SP2 with Microsoft Office
Excel 2007 (12.0.6425.1000) SP2 MSO (12.0.6425.1000).

I don't want to uninstall and reinstall Office 2007 to try and correct the
issue becuase my local TSE at work doesn't want to have to do this to several
computers that are having the issue. He would rather find out what is
causing the issue and post a fix on the server for the users to install.

I've researched for hours on end trying to find out what might be causing
this but came up with nothing. I called Microsoft to see if they could help
but all they want to do is charge me $260 for an incident ticket. I'm upset
about that because the software was only installed on my computer in January
2009 and others, less than 2 weeks ago. I would expect a warranty to cover
the cost. They said that because the issue is occuring on computers
connected to a network and involves a macro, they have to charge for
Professional support.

Can anyone out there help?

Thanks for listening,

GPMichal

 

[Jacob Skaria]

Did you check the Security settings; are Macros enabled...

Or

Do you use any other macros...

If this post helps click Yes

 

[gpmichal]

Thanks, Jacob.

I'm pretty sure that macros are enabled on all of the computers. I'll check
tomorrow. And, yes, I have several macros in the particular project.

GP

 

[Jacob Skaria]

Fine.Also check whether Application.EnableEvents is set to True and not to
False...

If this post helps click Yes

 

[gpmichal]

Thanks, Jacob.

Acutally, my last response was a little misleading. I have plenty of
workbooks that are loaded with macros but for testing purposes, all I was
doing is creating a new workbook and inserting;

into the ThisWorkbook object.

Then I would try to close the wb. If it wouldn't close, then the code
worked; if it did close, then the code isn't working.

So, therefore, the Application.EnableEvents statement doesn't apply here
(although I did write it into the above simple code just to be sure).

I checked all the macro settings and tried changing most of them and
retesting but to no avail. I do think we may be on the rirght track though.
It must be some type of global setting somewhere. Either on the local
machine or somewhere on the network imposed by the administrator (but then
again, if it was imposed by the admin, then none of the computers on the
network would let the code work).

Any other ideas?

GP

 

[Jacob Skaria]

Try setting the Security level to low in Tools|MAcros|Security

 

[Jacob Skaria]

Instead of copying from VBE; left treeview, double click 'This Workbook' and
then drop down to get the event... and then put Cancel = True...

 

[gpmichal]

Yes, the macro security is set as low as it goes and I set up the code
exactly the way you mentioned. Remember...all computers tested are set up
exactly the same way regarding the macro settings and the way the code is
created.

 

[gpmichal]

Anyone have any other ideas out there?

Yes, the macro security is set as low as it goes and I set up the code
exactly the way you mentioned. Remember...all computers tested are set up
exactly the same way regarding the macro settings and the way the code is
created.

 

[johnywhy]

Enable Events

Application.EnableEvents statement doesn't apply here

that's incorrect-- event code, such as workbook-close, will not run unless events are enabled BEFORE you close the book.

(although I did write it into the above simple code just to be sure).

that will not work. events must be enabled BEFORE you close the book. so you cannot enable events inside the workbook-close procedure.

to test, enter the following in your immediate pane in the vba editor:

?Application.EnableEvents

if false, enter the following:

Application.EnableEvents = True

then close the book.