Problem recording entry time

[JR Hester]

xecel 2007 on WindowsXP
I need to time stamp entries in a spreadsheet. I thought the NOW() function
would do teh trick, but alas, I was mistaken. In column "B" I used the
formula
=if(A#>0, NOW(), "")
which worked great, until I moved to the next line and made an entry into
column A, that is when the both entries in column B were updated to the
current time.

Is there a way to record the time a cell changes from empty to non empty
status, without updating every other cell in the column? We are simply
attempting to automate a call log-- shouldn't be this difficult.

Anyone have a solution for my little mind twister today

Thanks in advance for the fantatsic help this forum provides

 

[Mike H]

Hi,

To get a static time right click your worksheet, view code and paste this
code in. Any entry in column A makes column B populate with a static NOW

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
With Target.Offset(, 1)
.Value = Now
.NumberFormat = ("dd/mm/yyyy hh:mm")
End With
Application.EnableEvents = True
End If
End Sub

Mike

 

[JR Hester]

Thank you , that works just as we had hoped for.

Hi,

To get a static time right click your worksheet, view code and paste this
code in. Any entry in column A makes column B populate with a static NOW

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
With Target.Offset(, 1)
.Value = Now
.NumberFormat = ("dd/mm/yyyy hh:mm")
End With
Application.EnableEvents = True
End If
End Sub

Mike

 

[Mike H]

Your welcome

Thank you , that works just as we had hoped for.

 

[JR Hester]

Mike,

Thanks for that solution yesterday. Admittedly I am not macro literate at
this time. Now my assocaites need another version of that routine. They want
to add a second version in teh same worksheet that targets column G for data
entry and palce teh static NOW into column "F". I thought just making some
changes to teh code might get the results we were looking for. I tried
changing the offset to -1, 7 or even leaving at just 1, without any luck.

Here is the last code I tried, am I any where close or is this worksheet
change macro limited to one occurrence in a worksheet?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
With Target.Offset(, 1)
..Value = Now
..NumberFormat = ("hh:mm:ss")
End With
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change2(ByVal Target2 As Range)
If Target2.Cells.Count > 1 Or IsEmpty(Target2) Then Exit Sub
If Not Intersect(Target2, Range("G:G")) Is Nothing Then
Application.EnableEvents = False
With Target2.Offset(, -1)
..Value = Now
..NumberFormat = ("hh:mm:ss")
End With
Application.EnableEvents = True
End If
End Sub

Thanks again for any guidance you can offer.