Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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. |
#2
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
In the event code, start the first line with:
Application.EnableEvents = False and end with Application.EnableEvents = True Bob Umlas Excel MVP "Peter Danes" wrote in message ... 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. |
#3
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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 "Bob Umlas" píše v diskusním příspěvku ... In the event code, start the first line with: Application.EnableEvents = False and end with Application.EnableEvents = True Bob Umlas Excel MVP "Peter Danes" wrote in message ... 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. |
#4
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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" wrote in message ... 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 "Bob Umlas" píše v diskusním příspěvku ... In the event code, start the first line with: Application.EnableEvents = False and end with Application.EnableEvents = True Bob Umlas Excel MVP "Peter Danes" wrote in message ... 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. |
#5
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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 "Shauna Kelly" píše v diskusním příspěvku ... 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" wrote in message ... 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 "Bob Umlas" píše v diskusním příspěvku ... In the event code, start the first line with: Application.EnableEvents = False and end with Application.EnableEvents = True Bob Umlas Excel MVP "Peter Danes" wrote in message ... 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. |
#6
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
|
#7
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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 |
#8
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming,microsoft.public.word.vba.userforms
|
|||
|
|||
Force event code to NOT respond
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 ISTOO Word MVP site: http://www.word.mvps.org |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force Worksheet_Change event | Excel Programming | |||
How can I force my code to wait | Excel Programming | |||
excel.application.run macro1 -- added code to respond "yes" to a inputbox | Excel Programming | |||
Force code to compile | Excel Programming |