Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet change event
I have a worksheet change event (located in ThisWorkbook) that I need
to temporarily disable while another macro runs. How do I do this? Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet change event
Tim,
make sure that if you could meet an error condition (worksheet, range doesn't exist for instance), that you trap it and reset events in this case. otherwise you might switch them off period. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "John Wilson" wrote in message ... Tim, Application.EnableEvents = False set it back to True when you're done with that part of the code that would have fired the event. John Tim wrote: I have a worksheet change event (located in ThisWorkbook) that I need to temporarily disable while another macro runs. How do I do this? Thanks for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet change event
Application.EnableEvents = False will disable ALL events. If you don't want to do that, then you
can use a public variable (in a standard module) In the standard module: Public Flag As Boolean Sub AnotherMacro() Flag = True 'run your code here Flag = False End Sub In ThisWorkbook module: Sub Worksheet_Change(.....) If Flag Then Exit Sub 'the "real" code here End Sub Check out the Public statement in Help for more information about variable scope. On 14 Jul 2003 10:12:27 -0700, (Tim) wrote: I have a worksheet change event (located in ThisWorkbook) that I need to temporarily disable while another macro runs. How do I do this? Thanks for the help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet change event
John,
You are right, but Myrna points out a particular instance which may not be appropriate, and so is expanding the bag of tricks so to speak. As ever, its a case of horses for courses. With a full toolkit, we can pick the most appropriate. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "John Wilson" wrote in message ... Myrna, Just a question of proper procedure. Usually, just a single line of code will cause an event to fire. If Application.EnableEvents is set to False just before that line of code and back to True just after it, isn't this the easiest way to go about insuring that the event won't fire? John Myrna Larson wrote: Application.EnableEvents = False will disable ALL events. If you don't want to do that, then you can use a public variable (in a standard module) In the standard module: Public Flag As Boolean Sub AnotherMacro() Flag = True 'run your code here Flag = False End Sub In ThisWorkbook module: Sub Worksheet_Change(.....) If Flag Then Exit Sub 'the "real" code here End Sub Check out the Public statement in Help for more information about variable scope. On 14 Jul 2003 10:12:27 -0700, (Tim) wrote: I have a worksheet change event (located in ThisWorkbook) that I need to temporarily disable while another macro runs. How do I do this? Thanks for the help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet change event
Bob,
Thanks for the heads up. I've used coding similar to what Myrna pointed out in some of my projects. I questioned my post after reading yours and Myrna's thinking that I did something wrong or there was a better way. With a full toolkit, we can pick the most appropriate. How very true. There are many questions in the ng's where you could carried away writing a book with all the different ways to answer it. There probably will never be a "full" toolkit though, which is good so that we can keep busy "trying" to fill it. Take care, John Bob Phillips wrote: John, You are right, but Myrna points out a particular instance which may not be appropriate, and so is expanding the bag of tricks so to speak. As ever, its a case of horses for courses. With a full toolkit, we can pick the most appropriate. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "John Wilson" wrote in message ... Myrna, Just a question of proper procedure. Usually, just a single line of code will cause an event to fire. If Application.EnableEvents is set to False just before that line of code and back to True just after it, isn't this the easiest way to go about insuring that the event won't fire? John Myrna Larson wrote: Application.EnableEvents = False will disable ALL events. If you don't want to do that, then you can use a public variable (in a standard module) In the standard module: Public Flag As Boolean Sub AnotherMacro() Flag = True 'run your code here Flag = False End Sub In ThisWorkbook module: Sub Worksheet_Change(.....) If Flag Then Exit Sub 'the "real" code here End Sub Check out the Public statement in Help for more information about variable scope. On 14 Jul 2003 10:12:27 -0700, (Tim) wrote: I have a worksheet change event (located in ThisWorkbook) that I need to temporarily disable while another macro runs. How do I do this? Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change Event | Excel Discussion (Misc queries) | |||
Worksheet change Event | Excel Worksheet Functions | |||
Worksheet Change event | Excel Discussion (Misc queries) | |||
Worksheet Change Event | Excel Discussion (Misc queries) | |||
Worksheet Row Change event | Excel Discussion (Misc queries) |