Disable Events While Running Macro

  • Hello


    I am using the Worksheet_Change function behind a worksheet that executes some code every time a change is made on the worksheet.


    ie.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    End sub


    Is it possible to disable this function while running another macro? Therefore, when I enable Screen.Updating, the Worksheet_Change function will not be triggered.


    Many thanks to anyone who can help.


    Johno

  • Re: Disabling Worksheet_change


    Hi


    You can enable/disable worksheet events by toggling Application.EnableEvents - for example, assuming you want to run Sub Test which will change some sheet values, but you don't want the relevant sheet's Worksheet_Change event to fir you would include this:


    Code
    Sub Test()
    Application.EnableEvents = False  'turn events off!
    'rest of your code
    Application.EnableEvents = True 'turn events back on - this is critical, otherwise no events will fire again
    End Sub


    Make sense?


    Richard

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!