Thread: Run Macro on cell change from streaming data (not manual user input)

You'd have to run it on a timer basis. In other words monitor the value of the stock(s) every second and if it changes from the previous value then operate on it in the way that you want to.

Hm, are you sure a timer-basis is the only way? The absolute best would be for it to run on a cell change.

Agreed but I can't think of another way to monitor a cell otherwise. Sorry.

No problem, do you have an example of how one could do this? Say I have a macro that I only want to run once the cell changes, but I will check every 1 second or so if the cell has actually changed...

*Double post*

hi,

i was searching online for a solution to the exact same problem and found this post.
Your code works perfectly! however, it seems that it only works twice.. could you help?

Adrian

Originally Posted by Test123Test

Okay I figured it out finally. I'm going to explain here in case someone have similar requests in the future:
You have to use the Worksheet_Calculate() function if you want to run any macros automatically on cells updated by formulas or by streaming data. Worksheet_Change or anything else will NOT work, at least from my experience.

Anyways I'll just post my entire code below. This code will make you able to run macros based on cell updates from formulas or streaming data, guaranteed:

Sub Worksheet_Calculate()
    Static bSkipMacro
    If bSkipMacro = True Then Exit Sub
    bSkipMacro = True
    If Sheets("sheet1").Range("Your Range").value <> olval Then
        "Enter what you want to do here (Call, Run Macro etc)"
        olval = Sheets("sheet1").Range("Your Range (same as previous)").value
        bSkipMacro = False
    End If
End Sub

Then you need to make a new module, name it "module1" or whatever. Enter this text

Public olval As Double

And you're good to go.