Time stamping - automated by worksheet update
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
-----Original Message-----
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
In article ,
"George" wrote:
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
.
|