Capturing Time

[The Excelerator]

I had a project in excel in which I wanted a macro to capture the current
time and paste it into a cell as an absolute value of that time instance,
whilst the time continued to tick forward, and thus being able to once again
capture a fresher record of the time at a later date by doing the same.

Previously I had used the =now() [in cell A1] and formatted the cell for the
time only and pasted the "value" to another cell [cell B1]. What i found was
that the =now() function was not aiding the advancement of the time in REAL
time and only updated it upon pressing the dlete key in an unused cell.
I gues what I might need is a REAL TIME clock that continues to tick the
seconds whilst the sheet is open. Can that be done or is there an easier way.

Thanks

 

[Tom Ogilvy]

You could use the change event to put the current time in a cell. This would
trigger whenever a cell is edited. You would put an if statement in the
change event so that the time value would only be annotated when changes were
made within a specified range of cells. This sound like what you want (a
time stamp capability).

right click on the sheet tab and select view code. In the resulting module
at the top, in the left dropdown select worksheet and in the right dropdown
select change

Private Sub Worksheet_Change(ByVal Target As Range)

End sub

should appear. Put your code here. For example to timestamp any entry in
column C with a timestamp in column D same row:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler

If Target.Column = 3 then
application.EnableEvents = False
Target.offset(0,1).Value = Now
Target.offset(0,1).Numberformat = "mm/dd/yyyy hh:mm"
Columns(4).Autofit
end if
errHandler:
Application.EnableEvents = True
end Sub