How to run a macro when a cell updates through refreshing of data from web?

Question

When the column is refreshing nothing is happening but when I go to the cell and change the value then it is changing.

I want when cells update through refresh it should run.

The column updates but the code doesn't trigger the macro.

Also tried Worksheet_Calculate().

The column is linked with online stock data from NSE website.

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("B:B")) Is Nothing Then
        MsgBox "Cell Value Changed"
        Call MyMacro()
    End If
End Sub

On internet just told to use Worksheet_Calculate().

Also if trying to update the cell which is equal to a cell in Range("B:B"), the value changes but macro doesn't trigger.

They are updating from web query the data is from nseindia.com and I am doing it `From Web` under `DATA` section — Ripper, Aug 08 '19 at 14:17
If you're working with a QueryTable, then you may need to do something like this and add an event-handler for it: https://stackoverflow.com/a/36342211/1467082 — David Zemens, Aug 08 '19 at 15:05

TourEiffel · Accepted Answer · 2019-08-08T15:10:23.447

Maybe give this a try by using Workbook_SheetChange instead of Worksheet_Change

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("B:B")) Is Nothing Then
        MsgBox "Cell Value Changed"
        Call MyMacro()
    End If
End Sub

Note that you need to put your code in ThisWorkbook and not your module

Edit : to test the Answer :

Sub TryMe()
For i = 1 To 100
Cells(2, 2).Value = i
Next 
End Sub

TryMe Should be added inside a module like below

Workbook_SheetChange hould be added inside ThisWorkbook like below

When we execute test module we should have stuff like this:

and so on..

EDIT 2 If Value are changed by formula :

Give this a try :

This code should be placed in the sheet you are using (in my exemple Sheet1)

Private Sub Worksheet_Calculate()
    Dim rng As Range
    Set rng = Range("B:B")
    If Not Intersect(rng, Range("B:B")) Is Nothing Then
    MsgBox "Cell Value Changed"
    End If
End Sub

In a Module execute this code once :

Sub TryMe()

ActiveWorkbook.RefreshAll
Application.Calculation = xlAutomatic

End Sub

then this should work

score 0 · Answer 2 · answered Aug 08 '19 at 15:21

You can use events of QueryTable object behind the table linked to a web source. To do that, you first need to create a class module. Let's call it clsQryTebleEvents. In that module place a WithEvents variable of type Excel.QueryTable and set it to the QueryTable for which you want to capture events. Here's the code for clsQryTableEvents:

Option Explicit

Private WithEvents qryTable As Excel.QueryTable

Private Sub Class_Initialize()
    'QueryTable connected to a webpage is on Sheet1, and it's the only table on that sheet, so we can access it with ListObjects(1)
    Set qryTable = Sheet1.ListObjects(1).QueryTable
End Sub

Private Sub Class_Terminate()
    'Free Memory
    Set qryTable = Nothing
End Sub

'You can use other events as well
Private Sub qryTable_BeforeRefresh(Cancel As Boolean)
    MsgBox "Refresh is about to start!", vbInformation
End Sub

Next, you need to initialize a variable of this class. You can declare a Public variable inside a standard module and the use Workbook_Open event to instantiate it. Code in a standard module:

Option Explicit

Public objQryTable As clsQryTableEvents

Code in ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
    Set objQryTable = New clsQryTableEvents
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Free memory
    Set objQryTable = Nothing
End Sub

All done! Next time you open the workbook, the objQryTable will be initialised and will start listening to refresh events.

How to run a macro when a cell updates through refreshing of data from web?

2 Answers2