disable events while macro is running

[ben]

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

"ben" <ben@discussions.microsoft.com> wrote in message
news:ECC00922-BB22-4C90-9714-B28E174B11A6@microsoft.com...
> 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.

[ben]

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" <ben@discussions.microsoft.com> wrote in message
> news:ECC00922-BB22-4C90-9714-B28E174B11A6@microsoft.com...
> > 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]

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

Cheers
JulieD

"ben" <ben@discussions.microsoft.com> wrote in message
news:2B9A8B0C-25AF-48BB-ADFB-B6A2393EAA51@microsoft.com...
> 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" <ben@discussions.microsoft.com> wrote in message
>> news:ECC00922-BB22-4C90-9714-B28E174B11A6@microsoft.com...
>> > 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.
>>
>>
>>

[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

In article <2B9A8B0C-25AF-48BB-ADFB-B6A2393EAA51@microsoft.com>,
ben@discussions.microsoft.com 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" <ben@discussions.microsoft.com> wrote in message
> > news:ECC00922-BB22-4C90-9714-B28E174B11A6@microsoft.com...
> > > 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.
> >
> >
> >
>

[ben]

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 <2B9A8B0C-25AF-48BB-ADFB-B6A2393EAA51@microsoft.com>,
> ben@discussions.microsoft.com 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" <ben@discussions.microsoft.com> wrote in message
> > > news:ECC00922-BB22-4C90-9714-B28E174B11A6@microsoft.com...
> > > > 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.
> > >
> > >
> > >
> >
>

[ynotravid]

...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

Thanks Tushar, I was searching this forum to figure out how-to enable/disable events in a form. I guess I can't. I wanted to use something other than flags in an attempt to avoid spaghettiizing my code (I've got about 40 controls and 700 lines of code) but, I guess sometimes you just can't avoid it.

Thanks again,

-Tony