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.

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.

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.

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.

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.