How to turn off standard control functions

  • Thread starter Thread starter JL
  • Start date Start date
J

JL

I am writing some arrays that were filled with data from text files into
cells and every time I write to a cell the code jumps to the
Worksheet_Change() function. I do have some conditions in there that get
checked for use in other places but I would like to turn that off for some of
my operations. What is the code for turning off these functions? Thank you!
 
One way is to just tell excel to stop looking for anything that would fire any
event, do the work and tell it to start looking again.

Application.enableevents = false
'your code that does lots of stuff
application.enableevents = true
 
Daves suggestion is good but if the cells where you check for 'other stuff'
are in a common block you may like to consider at the top of your
worksheet_change sub

Set isect = Application.Intersect(Range("rg1"), Range(Target)) ' where
Target is the standard parameter referring to the actual cell where the
change occurred
If isect Is Nothing Then Exit Sub
 
But if you do use this kind of thing:

Set isect = Application.Intersect(Range("rg1"), Range(Target))

You'd want to use:

Set isect = Application.Intersect(Range("rg1"), Target)
or qualify that "rg1" range:
Set isect = Application.Intersect(me.Range("rg1"), Target)
 
Back
Top