Disable Worksheet change event

[Tim]

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.

 

[Bob Phillips]

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.

 

[Myrna Larson]

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.

 

[Bob Phillips]

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.

 

[John Wilson]

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