Prev: Copy range based on input
Next: Copying a variable nuimber of rows
From: JL on 10 May 2010 21:34
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!
From: Dave Peterson on 10 May 2010 21:43
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



JL wrote:

> 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!

--

Dave Peterson
From: K_Macd kmacdonald "A_T" activ8 ''''D O T'''' net [ST OP] on 10 May 2010 23:17
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


--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Dave Peterson" wrote:

> 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
>
>
>
> JL wrote:
>
> > 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!
>
> --
>
> Dave Peterson
> .
>
From: Dave Peterson on 11 May 2010 08:01
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)


K_Macd wrote:

> 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
>
>

--

Dave Peterson
From: Jeffy on 17 May 2010 16:38
Thank you all very much!

"JL" wrote:

> 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!
 | 
Pages: 1
Prev: Copy range based on input
Next: Copying a variable nuimber of rows