Time stamping - automated by worksheet update

  • Thread starter Thread starter George
  • Start date Start date
G

George

Does anyone have a tip how to have entered a static date
and time stamp based upon a user entering anything in an
adjacent cell? I am looking to capture the time and date
of user data entries so I can track when those entries
are entered into different cells on a worksheet.

If I use the now() function to capture when an entry is
made, it doesn't get saved as a static entry, so the
value changes whenever the Enter key is hit. I have made
a macro that enters a date or time stamp into a cell, but
that means a user has to initiate the macro, which would
be an extra step and the user would likely forget to
invoke it. I wish to simplify it so the macro is envoked,
or a function is called whenever the spreadsheet is
updated (the Enter key is hit) but that the static time
and date don't get changed unless the cell with the data
entry is changed.

Thanks for any suggestions.

-George
 
One way:

Assume you want a date/time to appear whenever an entry is made by
the user in column A.

Put this in the worksheet code module (right-click on the worsheet
tab and choose view code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then ' Entry in column A
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
End With
End Sub
 
First, thanks very much for your reply. Your help is
greatly appreciated.

Assume col A, rows 5 to 10 are set for a user to enter
data, and I wish to time stamp in the respective rows in
Col B the time when a user enters or modifies the data in
col A.

What formula would you use to place a static time stamp
in Col B and maybe a date stamp in Col C, or some such
layout?

-George
 
Working with the shared workbook options will not give me
the static time stamps for changes in specific cells I am
looking for.

So if Col A, Row 5 has text added to it, when the user
finishes editing the cell and keys the Enter key, Col B
Row 5 will have a time stamp when the Enter key was made.
When an entry is made in Col A, Row 6 is made, I wish to
capture when that edit is made without taking away the
entry in Col B, Row 5. (ie the row above) etc. etc.

-----Original Message-----

Have you looked at the share workbook options??


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
Click to expand...
step guide to creating financial statements
 
Modify my initial code slightly:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A10")) Is Nothing Then
With .Offset(0, 1)
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
With .Offset(0, 2)
.NumberFormat = "dd mmm yyyy"
.Value = Date
End With
End If
End With
End Sub
 
Back
Top