PDA

View Full Version : Worksheet_Change and RTD Link



maninjapan
01-24-2012, 05:31 AM
I am using an RTD link to pull data from my trading platform to excel. I am trying to record the data everytime it changes. I am currently attempting to use Worksheet_Change but even when the data in the cell with the RTD link changes it doesn't seem to be recognizing the change and therefore not executing the recording macro. (The macro worked fine during testing with manual changes to the cells).

Is there anyway around this to have excel recognize the changes in the RTD linked value?

Thanks

maninjapan
01-24-2012, 05:33 AM
Here is a copy of what I am working with if that helps

Aflatoon
01-24-2012, 06:57 AM
The change event only responds to a change in literal values, not formulas. I do not know of any particularly good solutions - you could use the calculate event and test the range for changes (you need to store the old values each time, either in an array, or in the cell's ID property for example) as one option.

maninjapan
01-24-2012, 08:03 AM
Aflatoon, thanks. Yes it looks like I got change mixed up with calculate.
as a short term fix I have placed each value on a different sheet. Probably not the most efficient way, but it seems to be working for me at the moment.
If anyone has any links to any examples on how to store the old value as Aflatoon mentions it would be much appreciated however!!

Thanks again!!

Aflatoon
01-24-2012, 08:13 AM
It is not clear to me where you want to store the data, but basically


Dim varData

Private Sub Worksheet_Activate()
varData = Range("B3:F3").Value
End Sub

Private Sub Worksheet_Calculate()
Dim n As Long
With Range("B3:F3")
For n = 1 To .Cells.Count
If varData(1, n) <> .Cells(n).Value Then
MsgBox "Changed value: " & .Cells(n).Value & " was " & varData(1, n)
End If
Next n
End With
End Sub