My spreadsheet imports data from Esignal (a popular stock charting application) and when certain conditions are met I need it to automatically run various macros. The spreadsheet is attached. In this simple example, the current time and the current price of the stock is updated realtime in cells A9:C9, with the preceding 4 mins worth of data in the preceding 4 rows. When the time reaches 10:47 hrs an "X" appears in the next cell, D9, and I've set up the macro to run automatically when the "X" appears in that cell. A single macro runs perfectly when using the example code below:
Module1 code
Sub Macro1()
Range("G1") = Range("G1") + 1
End Sub
Sub Macro2()
Range("H1") = Range("H1") + 1
End Sub
Sheet 1 code
Private Sub Worksheet_Calculate()
Static oldval
If Range("D9").Value = "X" And oldval <> "X" Then Macro1
oldval = Range("D9").Value
End Sub
However, ss soon as I try to add another automated macro, as below, Macro1 starts running continuously (or at least, every time the sheet refreshes as new price data comes in).
Private Sub Worksheet_Calculate()
Static oldval
If Range("D9").Value = "X" And oldval <> "X" Then Macro1
oldval = Range("D9").Value
If Range("D11").Value = "X" And oldval <> "X" Then Macro2
oldval = Range("D11").Value
End Sub
Any suggestions would be greatly appreciated!
Bookmarks