Worksheet_Calculation to create a Timer

[awriter]

Let me start off by trying to explain what I am trying to accomplish.
I have a column of data that updates occasionally from a RTD data feed. Each cell in the column updates at different times. I would like a timer to start in the column to the right of data when the data is updated. The timer would reset to 0 when an update occurs and the process would start over.

I'm sure that there is an easier way but this is the convoluted process I chose:

BTW- I am using XP Pro Excel 2003 sp2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
ThisRow = Target.Row
If Target.Value <> 0 Then
Range("F" & ThisRow) = Now()
Else
Range("F" & ThisRow) = 0
End If
End If

End Sub


Now this just posted the time that the cell change occurred in the column to the right. I then had a cell somewhere in the sheet tracking the current time through =Now() . I would then subtract the time stamped value of the cell change from the cell where the =Now() was and that was great. I had the timer that I wanted.

My problem is that using the RTD updates are considered Calculations not Changes, so it doesn't work!!!
I know that I need to use the Worksheet_Calculation command but I cannot get it to work at all.
I am worried that I will not be able to accomplish this because it sounds like if there was any calculation in the entire sheet ALL the timer would reset to Zero.

If anyone has any idea of how to accomplish this I am all ears. I would assume there is also an easier and more efficient way of doing this just that I cannot figure it out.
Let me finish by saying that my experience with VB is VERY limited.

Thank you in advance,
Adam

 

[stanleydgromjr]

Adam,

Try the below addition to your code (it copies the 'F & ThisRow' cell, and does an Edit, Past Special, Values, thereby removing the formula):


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
ThisRow = Target.Row
If Target.Value <> 0 Then
Range("F" & ThisRow) = Now()

Range("F" & ThisRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False

Else
Range("F" & ThisRow) = 0
End If
End If

End Sub


I am using Windows XP Professional SP2 and Excel 2003 SP2.

Have a great day,
Stan

 

[awriter]

Stan,
I tried the code and I'm still having the same issue. The problem is that the Target location (where I have the data updating through =RTD() and changes every so offten) is column #5 or "E". When the cell value changes by virtue of the RTD equation column "F" does not record the time of the update because the Worksheet_Change does not work for calculations. It DOES work fine if I manually make a change to any cell in column "E".
So I don't understatand the reasoning for your additional code? But I think I understand what you are trying to do. If I had the =RTD data in column #4 or "D" and if there was a change in the data and I were able to copy it to column "E" using the special paste Value function then it might work.
That leaves one problem I still have no idea of how to use the Worksheet_Calculation function????
I would think if I could accomplish what I want if I could get the Worksheet_Calculation to work and not have to do both (Worksheet_Calculation and then the Worksheet_Change).

Any other ideas,
Adam