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.

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

"ben" wrote in message
...
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.

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

"JulieD" wrote:

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

"ben" wrote in message
...
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.

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

Cheers
JulieD

"ben" wrote in message
...
thank you very much JulieD
Funny how some things are startingly easy

"JulieD" wrote:

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

"ben" wrote in message
...
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.

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

In article ,
says...
thank you very much JulieD
Funny how some things are startingly easy

"JulieD" wrote:

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

"ben" wrote in message
...
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.

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

"Tushar Mehta" wrote:

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

In article ,
says...
thank you very much JulieD
Funny how some things are startingly easy

"JulieD" wrote:

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

"ben" wrote in message
...
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.