How to suppress the worksheet_change event ?

[Sparkle99]

My VBA code is changing sheet2 (clearing cells, setting formats, adding formulas...)
sheet2 has worksheet_change to set the cells in bold when changed - based on a value set only when sheet2 is saved to a new file.
All of this is working fine.

Except, the code to change sheet2 is causing the worksheet_change event to happen (no value set, so the code is skipped) - BUT this does seem to be slowing things down quite a bit.

So, can worksheet_change be suppressed for a period ?

Can the worksheet_change code be added at the end of the main routine by VBA code ?

Any advice greatfully received.

 

[tusharm]

You can disable/enable events with the Application.EnableEvents property. For more see
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm
particularly the sections
Recursive calls to the same event procedure
and
An Excel specific trap: the EnableEvents property

My VBA code is changing sheet2 (clearing cells, setting formats, adding formulas...)
sheet2 has worksheet_change to set the cells in bold when changed - based on a value set only when sheet2 is saved to a new file.
All of this is working fine.

Except, the code to change sheet2 is causing the worksheet_change event to happen (no value set, so the code is skipped) - BUT this does seem to be slowing things down quite a bit.

So, can worksheet_change be suppressed for a period ?

Can the worksheet_change code be added at the end of the main routine by VBA code ?

Any advice greatfully received.