Comparing the update times of two cells

K

karlobrien

Hi,
If I have data updating in 2 cells, is it possible to compare the
update times of the 2 cells.
What I would like to know is if one cell stops updating and the other
one continues, then I know the data is bad coming into the cell that
has stopped.
I assume that I use a WorkSheet change event but how do I track what
cell stops updating?

Thanks,
K
 
M

Mike H

Hi,

Worksheet change 'may' work it depends on what triggers the update of your
data cells. If it doesn't then try worksheet calculate. Right click you sheet
tab, view code and paste this in. It assumes your 2 cells are a1 and E1
(Change tio suit) and it puts a timestamp in the cell to the right of each
when they update.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1,E1")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(, 1).Value = Time
Application.EnableEvents = True
End If
End Sub

Mike
 
K

karlobrien

Hi,

Worksheet change 'may' work it depends on what triggers the update of your
data cells. If it doesn't then try worksheet calculate. Right click you sheet
tab, view code and paste this in. It assumes your 2 cells are a1 and E1
(Change tio suit) and it puts a timestamp in the cell to the right of each
when they update.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1,E1")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Time
    Application.EnableEvents = True
End If
End Sub

Mike






- Show quoted text -

Hi,
thanks for the reply. I tested out the Worksheet_Change event but
that does not trigger when my cells are being updated. It only seems
to work when I type into the cell in question. I need the event to
fire automatically when the cell updates in real time. Can you
elaborate more on the Worksheet_Calculate event, I tried changing
Change to Calculate but this did not work

Thanks,
K
 
M

Mike H

Hi,

I had my doubts it would work. To try calculate go to where the code is and
on the top right dropdown select 'Calculate' and you get these 2 lines appear

Private Sub Worksheet_Calculate()

End Sub

Cut the code from the 'change' routine and paste it into this and see what
happens.

Mike
 
M

Mike H

Hi,

this should work. Alt +f11 to open Vb editor, double click 'This Workbook'
and paste this in on the right

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "MyMacro"
End Sub

The right click 'This workbook' , insert module and paste this in
Public dTime As Date
Public oldvalue As Long
Public oldvalue1 As Long
Sub MyMacro()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "MyMacro"

If Sheets("Sheet1").Range("A1").Value <> oldvalue Then
Sheets("Sheet1").Range("B1").Value = Time
End If

If Sheets("Sheet1").Range("A2").Value <> oldvalue1 Then
Sheets("Sheet1").Range("B2").Value = Time
End If

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("B1").Value

End Sub

Save the workbok and close and repoen it. Every 10 seconds it will check if
A1 & a2 on sheet 1 have been changed and if they have it will timestamp B1 &
B2.

Mike
 
K

karlobrien

Hi,

this should work. Alt +f11 to open Vb editor, double click 'This Workbook'
and paste this in on the right

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:10"), "MyMacro"
End Sub

The right click 'This workbook' , insert module and paste this in
Public dTime As Date
Public oldvalue As Long
Public oldvalue1 As Long
Sub MyMacro()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "MyMacro"

If Sheets("Sheet1").Range("A1").Value <> oldvalue Then
    Sheets("Sheet1").Range("B1").Value = Time
End If

If Sheets("Sheet1").Range("A2").Value <> oldvalue1 Then
    Sheets("Sheet1").Range("B2").Value = Time
End If

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("B1").Value

End Sub

Save the workbok and close and repoen it. Every 10 seconds it will check if
A1 & a2 on sheet 1 have been changed and if they have it will timestamp B1 &
B2.

Mike






- Show quoted text -

Thanks for the code, looking good now

One thing though

Should this:

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("B1").Value

Read As:

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("A2").Value
 
K

karlobrien

Thanks for the code, looking good now

One thing though

Should this:

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("B1").Value

Read As:

oldvalue = Sheets("Sheet1").Range("A1").Value
oldvalue1 = Sheets("Sheet1").Range("A2").Value- Hide quoted text -

- Show quoted text -

Hi Folks,
If I want to check the updates in the order of milliseconds, say 0.2
seconds, can I do this through the same method?
In the example above the code checks for updates every 10 seconds.

I was thinking along the lines of:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Problem is how do I get the code to use this to check for updates
every 0.2 seconds?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top