Worksheet Event - Trigger macro after cell changes linked to a value in a query

[gitmeto]

I have a sheet that contains a table generated by a repeating scan of a directory to detect when specific files are modified. On another sheet (Sheet("Linked")) I have a cell that is linked via a formula (=cellmodified) to the "Date Modified" value in the table generated by Power Query (PQ).

What I am attempting to do is run a macro on Sheet("Linked") when the cell value change on this sheet. Below you will find the code that I thought would trigger the macro but to no avail. Any assistance would be greatly appreciated!

Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Me.Range("C6").Address _
Then
  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False

    'Refresh the table
    ListObjects("table2").QueryTable.Refresh BackgroundQuery:=False
    
    'Restore Automatic Calucalation to run calculator
    
    Application.Calculation = xlCalculationAutomatic
    Call daily_calculate

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = True
  Application.DisplayStatusBar = True
  Application.EnableEvents = True
End If

End Sub

 

[gitmeto]

I didn't think I needed it either, but for a sanity check I threw it in. It didn't make a difference and no, I just checked on every sheet in the workbook. The current sheet I am working on is the only sheet that has code on it besides the Workbook module and a couple of non-sheet modules. I don't have any other change events configured. I also tried to change the calculation to manual but that didn't even help. It even runs the macro if I save the workbook.

 

[gitmeto]

You do not need the Exit Sub Line because if the value is not equal, it will not be executing the IF-End If. The other problem that you will face with Exit Sub line is that you have switched off events. They will not be turned back on.

Are you sure you do not have any Worksheet_Change Event (in the sheet code module) which may be triggering the code?

I think I figured it out. I modified the data type in PQ to match the data type on the spreadsheet. At this point it is not kicking off the macro when I save it or enter a value into a cell on another sheet. So ?we are in the clear. Thanks again for steeping in. Hope I can return the favor in the future.