a pop up box when RTD changes value?

[TonyW]

I am using excel RTD exported from thinkorswim. I like to get a simple pop up box whenever an RTD value changes. I looked online and found an example:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$4" Then
MsgBox Target.Value & " " & Target.Offset(0, 1).Value
End If
End Sub

Unfortunately it only works with actual text values, excel can't detect RTD changes using the above method. Any ideas?

 

[status1]

I don't have an answer just curious why you need a pop up box when you can see the values change in the cell ?
Is that going to be use on the price ? I imagine on SPX you would have a pop up box every second if not more often and would be distracting
Wouldn't an alert be better ?

 

[jim leahy]

i ran into this problem before, if you use a different cell and equate it to the rtd cell you can detect that
cell changing. i don't remember if the change event worked for that or if i had to use a recalculate event.
but rtd usually updates frequently so a pop up may not be effective.

 

[TonyW]

I don't have an answer just curious why you need a pop up box when you can see the values change in the cell ?
Is that going to be use on the price ? I imagine on SPX you would have a pop up box every second if not more often and would be distracting
Wouldn't an alert be better ?

I have a different formula that uses RTD values which doesn't change that often in the day. It would just be more visible to me since I am tracking like 10 different screens.

 

[DaveN]

@TonyW as @jim leahy suggests, it takes another cell to perform a calculation from that incoming RTD feed. That Sub that you posted is designed as an Event Handler which is looking for a change to take place in the target F4 cell. The RTD updates aren't triggering that change.

If you create a new cell that's linked/references "=$F$4" to that RTD cell, it will be changing or recalculating with updating values in the target. If for some reason it does not, you could create a formula in that referencing cell like "=$F$4 * 1" or even in $F$4 directly like "= 1 * RTD(<syntax for your mid price or whatever>)"

Notice in the Sub() you posted that the code is looking for a change in $F$4. If you create a linked cell referencing $F$4, you'll need to change the VBA code to that new cell.

Also, since RTD runs behind Excel, if you are in the middle of an edit in another cell or calculating something else, this code waits, and it's possible that it might miss the event. So, other code running or worksheet editing that you are doing should be kept to a minimum if possible.

 

[TonyW]

Another person in other forum helped me out, it's basically same as you said. Thank you.