disable events while macro is running

[Guest]

Two-part question

I know there is a way to prevent events (such as worksheet_change) from
activating from a running macro. Eg. A user enters a value in "A3" and it
sets off macro. But a Macro changes the value in "A3" and no Macro is set off.

Question one, how is this done through VBA options.
Question two, Is there a way to set this option on/off programmatically.

 

[JulieD]

Hi Ben

i think you're after

application.enableevents = false
to turn it off and

application.enableevents = true
to turn it back on.

Cheers
JulieD

 

[Guest]

thank you very much JulieD
Funny how some things are startingly easy

 

[JulieD]

you're welcome - sometimes it's the old "can't see the forrest for the
trees" situation

Cheers
JulieD

 

[Tushar Mehta]

This is one of those instances where things are more complicated than
meets the eye.

It is *absolutely, positively, unconditionally, imperative* that you
reenable events. If you fail to do so, even in the event of a fault in
your code, XL will not enable them!

On Error Goto ErrXIT
Application.EnableEvents=False
'...
ErrXIT:
Application.EnableEvents=True

Also note that the above doesn't affect forms related events. AFAIK,
they cannot be controlled as elegantly as above. You basically need a
programmer-created-and-maintained flag (a boolbean) and code in each
event procedure to check if the actual event procedure code should or
should not be executed.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

absolutely about turning it back on, that was my first thought but good idea
to post it.