Stop Worksheet Change Macro Triggering When Referenced Data Refreshes But Doesn't Change

[JonGil]

Hi, I am new to VBA and trying to create a macro that triggers when a value in column A changes. My worksheet data is referenced from another worksheet and set to refresh every day. The problem I am encountering is when the worksheet refreshes even if no changes have occurred my worksheet change macro is triggering. I need it to trigger but only if one of the refreshed values has changed. Is this possible?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Set xRg = Range("A1", Range("A1").End(xlDown))
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save

    If Not xRgSel Is Nothing Then
        MsgBox "Updated"
    End If
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

 

[Joe4]

Thanks Rory.

Never having used that method before, I wasn't sure if it worked like a link (which would not fire it), or if it was like a copy/paste (which would).
Thanks for clarifying!

 

[jolivanes]

Events In Excel VBA

www.cpearson.com

 

[NoSparks]

Two questions regarding post #3...

1) Does the message box only appear once ?

2) If you copy the data in column A and paste it as values into a column off to the right, let's say column Z,
does the "triggered by range" remain the same when you refresh or does it now include column Z ?