Comparing the update times of two cells
 

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

Comparing the update times of two cells
 

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

" wrote:

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

Comparing the update times of two cells
 

On Sep 25, 9:08*am, Mike H wrote:
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



" wrote:
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- Hide quoted text -

- 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

Comparing the update times of two cells
 

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

" wrote:

On Sep 25, 9:08 am, Mike H wrote:
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



" wrote:
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- Hide quoted text -

- 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

Comparing the update times of two cells
 

Sorry,

Ignore that last post it won't work. let me think.

Mike

"Mike H" wrote:

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

" wrote:

On Sep 25, 9:08 am, Mike H wrote:
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



" wrote:
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- Hide quoted text -

- 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

Comparing the update times of two cells
 

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

"Mike H" wrote:

Sorry,

Ignore that last post it won't work. let me think.

Mike

"Mike H" wrote:

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

" wrote:

On Sep 25, 9:08 am, Mike H wrote:
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



" wrote:
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- Hide quoted text -

- 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

Comparing the update times of two cells
 

On Sep 25, 11:07*am, Mike H wrote:
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



"Mike H" wrote:
Sorry,

Ignore that last post it won't work. let me think.

Mike

"Mike H" wrote:

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

" wrote:

On Sep 25, 9:08 am, Mike H wrote:
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

" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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

Comparing the update times of two cells
 

On Sep 25, 2:42*pm, wrote:
On Sep 25, 11:07*am, Mike H wrote:





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

"Mike H" wrote:
Sorry,

Ignore that last post it won't work. let me think.

Mike

"Mike H" wrote:

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

" wrote:

On Sep 25, 9:08 am, Mike H wrote:
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

" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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- 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?