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

 

[NoSparks]

If no change occurred then the event wouldn't have been triggered.
For test purposes put a message box as the first line in the macro and it will tell you what is triggering the event each time it fires.

MsgBox "This was triggered by a change in range " & Target.Address

 

[JonGil]

Thanks for your reply. This returns the message below. The cells referenced are my entire active worksheet.

 

[jolivanes]

If you don't want things to change, you should have something different for "Worksheet_Change" event. That is what "Worksheet_Change" does.
You could set your target range as a column, multiple columns, Row, multiple rows, cell or multiple cells.
If any value changes, run a macro.

 

[JonGil]

If you don't want things to change, you should have something different for "Worksheet_Change" event. That is what "Worksheet_Change" does.
You could set your target range as a column, multiple columns, Row, multiple rows, cell or multiple cells.
If any value changes, run a macro.

I apologize, I don't quite understand your suggestion. I thought I could use the "Worksheet_Change" event to run a macro if something in the worksheet changed? Currently "Worksheet_Change" is running the macro every time the data refreshed even if nothing changes. Is there a better way of going about this?

 

[Joe4]

How exactly does this "data refresh" work?
"Worksheet_Change" will only run if a cell somewhere on the sheet is manually changed (typing something in, doing a copy/paste, etc).
It is not triggered if the values that change are formulas or links to other places.

 

[JonGil]

How exactly does this "data refresh" work?
"Worksheet_Change" will only run if a cell somewhere on the sheet is manually changed (typing something in, doing a copy/paste, etc).
It is not triggered if the values that change are formulas or links to other places.

My worksheet is getting its data from an external workbook. The data is refreshed every 24 hours. I then want to scan for changes and trigger an event based on any changes that have happened.

 

[Joe4]

The cells on this page that are being "refreshed", are they formulas/links to other places?
Or is data being phsycially copied and pasted into these ranges?

How the data gets updated matters GREATLY here. So we need to have a clear understanding how the data on this sheet gets updated.

 

[JonGil]

The cells on this page that are being "refreshed", are they formulas/links to other places?
Or is data being phsycially copied and pasted into these ranges?

How the data gets updated matters GREATLY here. So we need to have a clear understanding how the data on this sheet gets updated.

There is a master workbook stored on a SharePoint which is manually updated on a pretty frequent basis. The workbook I have created has used the Get Data from Web function to create a copy of this workbook. I have set the Refresh connection property to refresh every 24 hours. I want to compare this refreshed data to the data before the refresh and run macros based on changes.

 

[RoryA]

The change event is triggered whenever cells are changed in any way, which includes them being overwritten by a data refresh. It is not restricted to the cell values actually differing from their previous values (simply pressing f2 and then Enter on a cell will trigger it even though the cell value is the same as it was). You would have to store the old values somewhere prior to the refresh and then do a cell by cell comparison in some way.