none
Stop Macro On Worksheet_Change Event From Running When Change is Made By Another Macro RRS feed

  • Question

  • I have a macro that runs when a user makes a change to certain cells on a worksheet.

    I have a separate macro that makes changes to those same cells.

    How do I prevent the macro that runs on a user change from running when a target cell is updated by some other macro?

    The macro that runs when a user makes a change (the one I want to prevent from running when a macro makes a change) is in the Sheet1 Module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rTargetRange As Range
    
    Set rTargetRange = Range("A21:A121")
    
    If Not Application.Intersect(rTargetRange, Range(Target.Address)) _
           Is Nothing Then
        
        MsgBox "Cell " & Target.Address & " has changed."
    
    End If
    
    End Sub

    The macro that makes changes to the target cells that I want to prevent from triggering the Worksheet_Change event is in Module1:

    Sub TestUpdate()
    
    Dim lRecordNum As Long
    
    For lRecordNum = 21 To 31
        Cells(lRecordNum, 1).Value = lRecordNum
    Next lRecordNum
       
    End Sub

    I tried creating a public boolean "flag" that the update macro would set to true when it starts and back to false at the end but the "flag" is empty in the change event macro even when it correctly shows true in the update macro at the time the change event triggers.

    Any help or advice is appreciated.

    Thanks in advance.

    Thursday, March 28, 2019 3:23 PM

Answers

  • Hi There,

                   Easiest way is you can put below lines in your update macro before and after your code executes:

    Application.EnableEvents = False

    'Your Code

    Application.EnableEvents = True

    Another way is you can have a flag value set to True before running your update logic and when code finishes you can set it to false.

    Check same flag in _Change event and skip the code/logic.


    A user friendly computer first requires a friendly user

    • Marked as answer by dkingston Thursday, March 28, 2019 3:44 PM
    Thursday, March 28, 2019 3:32 PM

All replies

  • Hi There,

                   Easiest way is you can put below lines in your update macro before and after your code executes:

    Application.EnableEvents = False

    'Your Code

    Application.EnableEvents = True

    Another way is you can have a flag value set to True before running your update logic and when code finishes you can set it to false.

    Check same flag in _Change event and skip the code/logic.


    A user friendly computer first requires a friendly user

    • Marked as answer by dkingston Thursday, March 28, 2019 3:44 PM
    Thursday, March 28, 2019 3:32 PM
  • Aplication.EnableEvents is exactly what I needed and didn't know existed.

    Thanks very much! You're a rockstar!

    Thursday, March 28, 2019 3:45 PM