Force event code to NOT respond

[Peter Danes]

I have this problem in all three Office products which I regularly program,
Access, Excel and Word.

I write event handlers to respond to (for instance) a change event, but my
intention is to respond only to changes made by the user. The problem I have
often encountered is that somewhere in code I have reason to change the
contents of a form control and that also fires the change event. The only
way I have found so far to deal with this is to set some sort of global
variable (or sometimes the .Tag property) and have the event code check the
status of that global variable to determine whether it should in fact
respond to the change event, or just exit again immediately.

This works, but it's awkward to code and prone to logic bugs. Does anyone
know of a better way to deal with events that are triggered by code when I
want the event code to respond ONLY to activities by the user?

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Bob Umlas]

In the event code, start the first line with:
Application.EnableEvents = False
and end with
Application.EnableEvents = True

Bob Umlas
Excel MVP

 

[Peter Danes]

Many thanks, Bob. I didn't even know such a property (method?) existed, but
it sounds like exactly what I want to do - turn off event firing while
certain pieces of code run.

I just did some searching, though, and it appears to be unique to Excel. It
sounds like it should be a universal VBA command, but neither Word's nor
Access's Intellisense recognize it, nor does the Help have anything to say
in those apps. Still, it will solve the problem for Excel, which is a good
chunk of my work.

Thank you again,

Pete

 

[Shauna Kelly]

Hi Peter

Sadly, Word does not have an Application.EnableEvents property.

However, you can dummy one up yourself. To do so, create a global variable
named, for example, gbEnableEvents as a Boolean.

In the event handler set gbEnableEvents to False, let your code run, then
re-set gbEnableEvents to True.

Elsewhere, put something like
If gbEnableEvents Then
'your code here
Endif

Where necessary, you may need to store the value of the global variable, set
it to false and reinstate its value at the end of a procedure.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Peter Danes]

Hello Shauna,

thank you for your suggestion. Yes, that's the way I have been doing it, but
I don't like it very much and was hoping that someone had a better idea. It
requires the global variable test in EVERY event module that may be
triggered inadvertently, which is a pain in the fundament and easy to forget
as I add and remove various controls and their event handlers in the process
of tuning my app. It's much more convenient when it's possible to prevent
the event code from firing at all. Ideal would be a property in the control
that you could set to indicate what it should respond to; user changes, code
changes, both, neither, other things. Maybe someday...

Pete

 

[Albert D. Kallal]

Actually, you got a good answer for excel...

In ms-access, when you change the value of control in code.the after update,
and change events DO NOT fire...

So, I don't think you need any thing for ms-access solution....

 

[Peter Danes]

Actually, you got a good answer for excel...

I did, and I don't think that I implied otherwise in my response.

In ms-access, when you change the value of control in code.the after update,
and change events DO NOT fire...

So, I don't think you need any thing for ms-access solution....

I don't recall just now exactly which events are triggered by code in which
application, some are, some are not. My search was for a general, better
solution to unwanted event code activation than the global variables that I
have been using to date. I -do- need something for Access and Word both, and
it seems odd to me that something like Bob's EnableEvents trick isn't
available VBA-wide. Maybe in some future versions.

Pete

 

[Jean-Guy Marcil]

Peter Danes was telling us:
Peter Danes nous racontait que :

at all. Ideal would be a property in the control that you could set
to indicate what it should respond to; user changes, code changes,
both, neither, other things. Maybe someday...

I know where you are coming from... I have had to do this many times myself
(set a global variable, etc.).

But, if you think about it, having to remember to add a variable test every
time you add a control is pretty much the same as having to remember to
change/set a control property when you insert it... no?

What would be ideal, to my mind, is a property we could set at the userform
level (in the Initialize event for example) that would do the job for all
controls...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org