Temporarily Suspending Worksheet_Change() until other code is run.
 

Hi all,

I currently have a Worksheet_Change() procedure based upon values
changing in a certain column of my worksheet.

I now wish to add in some additional code that formats and sucks in
data from another sheet, and do not want the Worksheet_Change()
procedure to kick in until the new pre-formating code has been
completed.

Any ideas how I can accomplish this?

Regards,

andym

Temporarily Suspending Worksheet_Change() until other code is run.
 

Andy

Use

Application.EnableEvents = False

To turn events off and then set it to true at the end of your code. (*OR
anywhere the code may exit, e.g errors, branching, etc)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk


"andym" wrote in message
oups.com...
Hi all,

I currently have a Worksheet_Change() procedure based upon values
changing in a certain column of my worksheet.

I now wish to add in some additional code that formats and sucks in
data from another sheet, and do not want the Worksheet_Change()
procedure to kick in until the new pre-formating code has been
completed.

Any ideas how I can accomplish this?

Regards,

andym

Temporarily Suspending Worksheet_Change() until other code is run.
 

Set the Application .EnableEvents to False to prevent events from
triggering. Then set it back to True. E.g.,

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


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



"andym" wrote in message
oups.com...
Hi all,

I currently have a Worksheet_Change() procedure based upon
values
changing in a certain column of my worksheet.

I now wish to add in some additional code that formats and
sucks in
data from another sheet, and do not want the Worksheet_Change()
procedure to kick in until the new pre-formating code has been
completed.

Any ideas how I can accomplish this?

Regards,

andym

Temporarily Suspending Worksheet_Change() until other code is run.
 

Two ways -

Just before your routine starts to change cells and trigger the event

On error goto errH
Application.EnableEvents = False

' code

errH:
Application.EnableEvents = True


Or, in a normal module declare a global flag

Public bExit as Boolean

in your event code
If bExit then Exit Sub

Change bExit to True / False at start and when done, also use an error
handler. The bExit method is safer (no risk of not resetting application
settings) but slightly slower.

Regards,
Peter T

"andym" wrote in message
oups.com...
Hi all,

I currently have a Worksheet_Change() procedure based upon values
changing in a certain column of my worksheet.

I now wish to add in some additional code that formats and sucks in
data from another sheet, and do not want the Worksheet_Change()
procedure to kick in until the new pre-formating code has been
completed.

Any ideas how I can accomplish this?

Regards,

andym

Temporarily Suspending Worksheet_Change() until other code is run.
 

Thanks Chip ... worked a charm :)

Regards,

andym