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

 

[Siddharth Rout]

Worksheet_Change events doesn't trap formula changes. For that you have to use Workbook_SheetCalculate event which you have to place in ThisWorkbook code module. When the table changes, the linked formula will also change which can trigger the macro

Here is a basic example

1. Put a formula =Sheet1!A1+2 in cell A1 of Sheet2.

2. Place this ThisWorkbook code module.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sheet2.Range("A1").Value > 5 Then MsgBox "Value updated"
End Sub

3. Now go back to Sheet1 and type any value greater than 3 in cell A1 of Sheet1.

 

[gitmeto]

Worksheet_Change events doesn't trap formula changes. For that you have to use Workbook_SheetCalculate event which you have to place in ThisWorkbook code module. When the table changes, the linked formula will also change which can trigger the macro

Here is a basic example

1. Put a formula =Sheet1!A1+2 in cell A1 of Sheet2.

2. Place this ThisWorkbook code module.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sheet2.Range("A1").Value > 5 Then MsgBox "Value updated"
End Sub

3. Now go back to Sheet1 and type any value greater than 3 in cell A1 of Sheet1.

 

[Siddharth Rout]

I just refreshed the page again. I do not see a message except my quoted message. Were you able to make it work?

 

[gitmeto]

Thanks Siddharth. I was in the process of posting a reply must have clicked it before I was able to write anything.

I believe if I am to make this work from the ThisWorkbook module, I will need to modify the code to reference the query table on sheet2 as well as the modify the way that I call the macro that is to be run. I changed the conditional but when I updated the fileWatch table it did not update the query table for sheet2 and therefore did not run the macro. I have Range("C9") reference the last entry of the query table on Sheet2 through an INDEX function. Therefore, when the fileWatch is updated, I was hoping that it would triger the macro on the Thisworkbook module to check the conditional and subsequently run the macro if it was true.

Following your guidance I modified the code (see below). I believe I have called the macro on the sheet to run if the condition is true.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  
' Turn off Excel functionality to improve performance.
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
  
  
    If Sheet2.Range("C9").Value <> [filewatch] Then
      
        'Refresh the table
        Sheet2.ListObjects("table2").QueryTable.Refresh BackgroundQuery:=False
      
        'Restore Automatic Calucalation to run calculator
        Application.Calculation = xlCalculationAutomatic
        'Refresh the table
        Call Sheet2.GetLatest
  
    End If
  
' Turn on Excel functionality
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
      
End Sub

 

[Siddharth Rout]

Put a break point on

If Sheet2.Range("C9").Value <> [filewatch] Then

and see if it enters the IF-End If by pressing F8. Also check what is the value of [filewatch]

 

[gitmeto]

Thanks for the suggestion. What I had to do is remove the optimizations from the Workbook module, and actually call the correct macro to do the calculations ?‍. Once I did that, when condition was TRUE, the table updated and the macro that I wanted to do the calculations ran as intended.

Thanks for you help Siddharth. You put me down the right path.

Now to set this up as a loop for the 26 other tables to watch.

 

[Siddharth Rout]

Thanks for sharing the feedback! Much appreciated!

 

[gitmeto]

Seems as though we called victory too soon. It seems as though if there are any recalculations, or any entry into a cell, on the sheet that contains the table from the query, or anywhere else in the book, it causes a the macro to kick off. Even when I place a conditional to exit if they are equal. I tested by going to another sheet and entering a formula, as well as entering a value into a cell. Not I am stumped.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'filewatch is produced by a scan of a directory for the Date Modified property of a worksheet _
 that contains the data to be imported.
' Turn off Excel functionality to improve performance.
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

    If Sheet2.Range("C6").Value2 = [filewatch] Then Exit Sub
    If Sheet2.Range("C6").Value2 <> [filewatch] Then
        
        'Refresh the table
        Sheet2.ListObjects("table2").QueryTable.Refresh BackgroundQuery:=False
        'Refresh the table
        Call Sheet2.daily_calculate
    End If

' Turn Excel functionality back on.
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

 

[Siddharth Rout]

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?