I need to record the time and date that a cell changes
states. It may change 1-2 times a day or it may not change
for 2-3 days. It's actually a string value but I
manipulated some logic to get it down to a true-false.
That worked in manual testing but not in field
conditions. I'm trying to capture a change in a PLC value
that feeds my worksheet, updating every 2 seconds. I have
a lookup table to simplify it to a number change now if
that helps.

I Have been banging on this for weeks now and I just can't
get to work. I don't have a deep enough programming
knowledge to make the correct modifications to the
worksheet_Change statement.

Below is what I had: (Got it from an earlier post in here)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Target(1)
If Not Intersect(Target, Range("P5")) Is Nothing Then
If Target.Value = 0 Then
With Target.Offset(0, 1)
.NumberFormat = "hh:mm AM/PM"
.Value = Time
End With
End If
End If
End Sub

What I need:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from whatever
it was to ANYTHING else I need to record the time and date
in another cell(say Q5). Then when it changes again record
the new time and date in Q5

Hope I explained it clearly.
Win 2000
Excel 2000
Thanks,
Mike

Since P5 is a calculated cell, I'd use the Calculate event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from whatever
it was to ANYTHING else I need to record the time and date
in another cell(say Q5). Then when it changes again record
the new time and date in Q5

Mr/Mrs McGimpsey,

The workbook_open event code is generating a compile error:
Ambiguous name detected: Workbook_Open. I have another
open event on that page that sets calculate to auto.

Mike


-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.

Combine the two. You can only have one.

--
Regards,
Tom Ogilvy

"Mike K" wrote in message
...
Mr/Mrs McGimpsey,

The workbook_open event code is generating a compile error:
Ambiguous name detected: Workbook_Open. I have another
open event on that page that sets calculate to auto.

Mike


-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.

Tom,
Thanks, I figured that one out after some trial and
error. We just changed colors on our manufacturing line
and the time/date was captured as requested.
BTW Thanks again EVERYONE for the help. As usual the
resources in this group are wonderful. I am approached
here at work regularly for my Excel "expertise".

I must never reveal my secret...

Mike




-----Original Message-----
Combine the two. You can only have one.

--
Regards,
Tom Ogilvy

"Mike K" wrote in
message
...
Mr/Mrs McGimpsey,

The workbook_open event code is generating a compile
error:
Ambiguous name detected: Workbook_Open. I have another
open event on that page that sets calculate to auto.

Mike


-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting
it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.



.

I just noticed a small problem. When I close the sheet and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.

I would suspect the dOldP5 gets cleared before it runs or the value in P5
changes.

If dOldP5 is getting cleared, then you might want to store its value in a
defined name.

go to Insert=Name=Define
in Refersto put in =19 (use the current value of P5)

in the name box put in dOldP5 and click add

Save the file.

Put this in the worksheet code module

'Public dOldP5 As Double

Private Sub Worksheet_Calculate()
dim lVal as Double
lVal =
Application.Evaluate(thisworkbook.Names("dOldP5"). RefersTo)
With Range("P5")
If .Value < lVal Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
Thisworkbook.Names("dOldP5").Refersto = "=" & .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
Thisworkbook.Names("dOldP5").Refersto = _
"=" & Sheets(1).Range("P5").Value
End Sub

--
Regards,
Tom Ogilvy


"Mike K" wrote in message
...
I just noticed a small problem. When I close the sheet and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.

Tom,
P5 has not changed since 11:45 but it updates the
time everytime the workbook is closed and reopened. I
defined the name as directed but it still updates

Mike

-----Original Message-----
I would suspect the dOldP5 gets cleared before it runs or
the value in P5
changes.

If dOldP5 is getting cleared, then you might want to
store its value in a
defined name.

go to Insert=Name=Define
in Refersto put in =19 (use the current value of P5)

in the name box put in dOldP5 and click add

Save the file.

Put this in the worksheet code module

'Public dOldP5 As Double

Private Sub Worksheet_Calculate()
dim lVal as Double
lVal =
Application.Evaluate(thisworkbook.Names
("dOldP5").RefersTo)
With Range("P5")
If .Value < lVal Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
Thisworkbook.Names("dOldP5").Refersto
= "=" & .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
Thisworkbook.Names("dOldP5").Refersto = _
"=" & Sheets(1).Range("P5").Value
End Sub

--
Regards,
Tom Ogilvy


"Mike K" wrote in
message
...
I just noticed a small problem. When I close the sheet
and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting
it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.



.

The update is protected by the IF test:

If .Value < dOldP5 Then


there would be no time update unless it passes that test. To fail the test
at least either .Value has to change or dOldP5 has to change. My suggestion
was to put dOldP5 in a defined name making it even more "armor plated". You
will have to put in some msgboxes or something to tell you what is letting
the date/time get changed.

--
Regards,
Tom Ogilvy

"Mike K" wrote in message
...
Tom,
P5 has not changed since 11:45 but it updates the
time everytime the workbook is closed and reopened. I
defined the name as directed but it still updates

Mike

-----Original Message-----
I would suspect the dOldP5 gets cleared before it runs or
the value in P5
changes.

If dOldP5 is getting cleared, then you might want to
store its value in a
defined name.

go to Insert=Name=Define
in Refersto put in =19 (use the current value of P5)

in the name box put in dOldP5 and click add

Save the file.

Put this in the worksheet code module

'Public dOldP5 As Double

Private Sub Worksheet_Calculate()
dim lVal as Double
lVal =
Application.Evaluate(thisworkbook.Names
("dOldP5").RefersTo)
With Range("P5")
If .Value < lVal Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
Thisworkbook.Names("dOldP5").Refersto
= "=" & .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
Thisworkbook.Names("dOldP5").Refersto = _
"=" & Sheets(1).Range("P5").Value
End Sub

--
Regards,
Tom Ogilvy


"Mike K" wrote in
message
...
I just noticed a small problem. When I close the sheet
and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting
it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.



.