Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capturing Time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capturing Time
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 -- Regards, Tom Ogilvy "The Excelerator" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing Trend Data over time | Excel Discussion (Misc queries) | |||
VB Code Required for Capturing Time | Excel Discussion (Misc queries) | |||
Date Capturing by Time | Excel Discussion (Misc queries) | |||
capturing a cell value at a specific time of day | Excel Programming | |||
capturing a cell value at a specific time of day | Excel Programming |