Hi All,

I have a WorkBook_BeforeClose event procedure in which in a special case I
want to set Application.EnableEvents = false before a Thisworkbook.Close
statement in order to avoid re-calling the WorkBook_BeforeClose event
procedure. It works, but when next time opening the workbook
Application.EnableEvents = false, and I cannot automatically reset it to True
(e.g. in a WorkBook_open event procedure) because the False setting prevents
executing the WorkBook_open event.
How can I get out from this circulus vitiosus?

Thanks,
Stefi

Instead of disabling events, use a public variable

At the top of the thisworkbook module

Public bBlockEvents as Boolean

in the Beforeclose event at the top put in a line

if bBlockEvents then exit sub

then where you have the disable events statement replace it with

bBlockEvents = True

It should loose its value (become false) once the workbook is closed so you
won't have a problem on reopening.

--
Regards,
Tom Ogilvy


"Stefi" wrote in message
...
Hi All,

I have a WorkBook_BeforeClose event procedure in which in a special case I
want to set Application.EnableEvents = false before a Thisworkbook.Close
statement in order to avoid re-calling the WorkBook_BeforeClose event
procedure. It works, but when next time opening the workbook
Application.EnableEvents = false, and I cannot automatically reset it to
True
(e.g. in a WorkBook_open event procedure) because the False setting
prevents
executing the WorkBook_open event.
How can I get out from this circulus vitiosus?

Thanks,
Stefi

Thank you Tom for your reply, it's a clever solution. Meanwhile I realized
that better not to step into the trouble than trying to get out of it. My
problem was caused by setting Cancel to True at the wrong place. Leaving it
False in the special branch of the routine made unnecessary to apply an extra
ThisWorkbook.Close statement because in this way the effect of clicking the
close button could prevail and after exiting the BeforeClose routin it closed
the workbook without manipulating Application.EnableEvents.

Regards,
Stefi


€žTom Ogilvy€� ezt Ã*rta:

Instead of disabling events, use a public variable

At the top of the thisworkbook module

Public bBlockEvents as Boolean

in the Beforeclose event at the top put in a line

if bBlockEvents then exit sub

then where you have the disable events statement replace it with

bBlockEvents = True

It should loose its value (become false) once the workbook is closed so you
won't have a problem on reopening.

--
Regards,
Tom Ogilvy


"Stefi" wrote in message
...
Hi All,

I have a WorkBook_BeforeClose event procedure in which in a special case I
want to set Application.EnableEvents = false before a Thisworkbook.Close
statement in order to avoid re-calling the WorkBook_BeforeClose event
procedure. It works, but when next time opening the workbook
Application.EnableEvents = false, and I cannot automatically reset it to
True
(e.g. in a WorkBook_open event procedure) because the False setting
prevents
executing the WorkBook_open event.
How can I get out from this circulus vitiosus?

Thanks,
Stefi