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


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

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
.

Have you looked at the share workbook options?

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
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
In article ,
wrote:

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?