Bypass Worksheet_Change Sub
 

I have a Sub set up so when a user makes a change to certain fields it
validates the data. Users are able to add many rows of data and this
validation is fairly transparent to them. Occassionally data will be copied
into the current spreadsheet from another source and because of the number of
fields being simultaneously updated, the Worksheet_Change Sub takes a few
minutes to complete. I am confident the data being inserted already meets
the validation and am wondering if there is a way to Turn off the
Worksheet_Change Sub before I paste the data and then turn it back on after I
am complete?


Matt

Bypass Worksheet_Change Sub
 

Use the EnableEvents property. E.g.,

Application.EnableEvents = False
' your code here
Application.EnableEvents = True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Matt" wrote in message
...
I have a Sub set up so when a user makes a change to certain
fields it
validates the data. Users are able to add many rows of data
and this
validation is fairly transparent to them. Occassionally data
will be copied
into the current spreadsheet from another source and because of
the number of
fields being simultaneously updated, the Worksheet_Change Sub
takes a few
minutes to complete. I am confident the data being inserted
already meets
the validation and am wondering if there is a way to Turn off
the
Worksheet_Change Sub before I paste the data and then turn it
back on after I
am complete?


Matt

Bypass Worksheet_Change Sub
 

Nevermind.... Application.EnableEvents handled my problem.

Thanks anyway.

"Matt" wrote:

I have a Sub set up so when a user makes a change to certain fields it
validates the data. Users are able to add many rows of data and this
validation is fairly transparent to them. Occassionally data will be copied
into the current spreadsheet from another source and because of the number of
fields being simultaneously updated, the Worksheet_Change Sub takes a few
minutes to complete. I am confident the data being inserted already meets
the validation and am wondering if there is a way to Turn off the
Worksheet_Change Sub before I paste the data and then turn it back on after I
am complete?


Matt

Bypass Worksheet_Change Sub
 

As with any application setting make sure to use an error handler to reset
the setting in case of a crash.
sub Whatever()
on error goto errorhandler
Application.EnableEvents = False
' your code here
ErrorHandler:
Application.EnableEvents = True
exit sub
--
HTH...

Jim Thomlinson


"Matt" wrote:

Nevermind.... Application.EnableEvents handled my problem.

Thanks anyway.

"Matt" wrote:

I have a Sub set up so when a user makes a change to certain fields it
validates the data. Users are able to add many rows of data and this
validation is fairly transparent to them. Occassionally data will be copied
into the current spreadsheet from another source and because of the number of
fields being simultaneously updated, the Worksheet_Change Sub takes a few
minutes to complete. I am confident the data being inserted already meets
the validation and am wondering if there is a way to Turn off the
Worksheet_Change Sub before I paste the data and then turn it back on after I
am complete?


Matt