disable events while macro is running
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. |
disable events while macro is running
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. |
disable events while macro is running
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. |
disable events while macro is running
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. |
disable events while macro is running
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. |
disable events while macro is running
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. |
All times are GMT +1. The time now is 10:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com