Time ticker
 

how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

Time ticker
 

Put this in the Workbook_Open() event procedu
Private Sub Workbook_Open()

TimeOpened = Now()
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

and then insert a module and add this code:
Public TimeOpened As Date

Public Sub Ticker()

Workbooks("BookName").Worksheets("Sheet1").Range(" A1") = Now - TimeOpened
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

A few notes:
- The display of the date will "freeze" if you are editing a cell's contents
but will pick up with the correct time when you are done editing
- Since this runs every second it could be a nuisance and take up processing
time from other tasks - it could be adjusted to update less frequently, if
desired
- If you are not familiar with how to use event procedures, search this
forum on that topic
--
- K Dales


"Soniya" wrote:

how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

Time ticker
 

Soniya wrote:
how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

In a public code module enter:

Option Explicit

Dim Start As Variant
Dim StopNow As Boolean

Sub Star****ch()
Start = Now
StopNow = False
RunWatch
End Sub

Sub StopWatch()
StopNow = True
End Sub

Sub RunWatch()
Dim Elapsed As Variant
Dim TimeString As String

If StopNow Then Exit Sub
Elapsed = Now - Start
TimeString = Hour(Elapsed) & ":" & Minute(Elapsed)
TimeString = TimeString & ":" & Second(Elapsed)
ActiveSheet.Range("A1").Value = TimeString
Application.OnTime Now + TimeValue("00:00:01"), "RunWatch"
End Sub

Then in the code for ThisWorkbook enter

Private Sub Workbook_Open()
Star****ch
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Star****ch
End Sub

In any sheet A1 will then display how long you have been in that sheet.
Running StopWatch will stop the timer.

Hope that helps

-John Coleman

Time ticker
 

Just found a problem with the code if you try to close the workbook: the
OnTime method will reload the workbook. Not sure yet of the best solution,
but if I find one will let you know.
--
- K Dales


"K Dales" wrote:

Put this in the Workbook_Open() event procedu
Private Sub Workbook_Open()

TimeOpened = Now()
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

and then insert a module and add this code:
Public TimeOpened As Date

Public Sub Ticker()

Workbooks("BookName").Worksheets("Sheet1").Range(" A1") = Now - TimeOpened
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

A few notes:
- The display of the date will "freeze" if you are editing a cell's contents
but will pick up with the correct time when you are done editing
- Since this runs every second it could be a nuisance and take up processing
time from other tasks - it could be adjusted to update less frequently, if
desired
- If you are not familiar with how to use event procedures, search this
forum on that topic
--
- K Dales


"Soniya" wrote:

how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

Time ticker
 

John Coleman wrote:
Soniya wrote:
how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

In a public code module enter:

Option Explicit

Dim Start As Variant
Dim StopNow As Boolean

Sub Star****ch()
Start = Now
StopNow = False
RunWatch
End Sub

Sub StopWatch()
StopNow = True
End Sub

Sub RunWatch()
Dim Elapsed As Variant
Dim TimeString As String

If StopNow Then Exit Sub
Elapsed = Now - Start
TimeString = Hour(Elapsed) & ":" & Minute(Elapsed)
TimeString = TimeString & ":" & Second(Elapsed)
ActiveSheet.Range("A1").Value = TimeString
Application.OnTime Now + TimeValue("00:00:01"), "RunWatch"
End Sub

Then in the code for ThisWorkbook enter

Private Sub Workbook_Open()
Star****ch
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Star****ch
End Sub

In any sheet A1 will then display how long you have been in that sheet.
Running StopWatch will stop the timer.

Hope that helps

-John Coleman

I noticed that my code has a problem if you have a chart sheet. You can
replace the main module with:

Option Explicit

Public start As Variant
Public StopNow As Boolean

Sub Star****ch()
start = Now
StopNow = False
RunWatch
End Sub

Sub StopWatch()
StopNow = True
End Sub

Sub RunWatch()
Dim Elapsed As Variant
Dim TimeString As String

If StopNow Then Exit Sub
On Error GoTo Err_Handler
Elapsed = Now - start
TimeString = Hour(Elapsed) & ":" & Minute(Elapsed)
TimeString = TimeString & ":" & Second(Elapsed)
ActiveSheet.Range("A1").Value = TimeString
Application.OnTime Now + TimeValue("00:00:01"), "RunWatch"
Exit Sub

Err_Handler:
'Must be on a chart sheet!
StopNow = True
End Sub


Also,
While I haven't experienced the problem that K. Dales had, but I
worry that it might occur. To be safe you could add the following to
your This Workbook code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim start As Single
StopWatch
start = Timer
Do While Timer <= start + 1
Loop
End Sub

This will give enough time to stop the watch before closing.

Hope that helps a bit more

-John Coleman

Time ticker
 

http://www.cpearson.com/Excel/ontime.htm

has the solution.

--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
Just found a problem with the code if you try to close the workbook: the
OnTime method will reload the workbook. Not sure yet of the best
solution,
but if I find one will let you know.
--
- K Dales


"K Dales" wrote:

Put this in the Workbook_Open() event procedu
Private Sub Workbook_Open()

TimeOpened = Now()
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

and then insert a module and add this code:
Public TimeOpened As Date

Public Sub Ticker()

Workbooks("BookName").Worksheets("Sheet1").Range(" A1") = Now -
TimeOpened
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

A few notes:
- The display of the date will "freeze" if you are editing a cell's
contents
but will pick up with the correct time when you are done editing
- Since this runs every second it could be a nuisance and take up
processing
time from other tasks - it could be adjusted to update less frequently,
if
desired
- If you are not familiar with how to use event procedures, search this
forum on that topic
--
- K Dales


"Soniya" wrote:

how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks

Time ticker
 

Thanks Tom (& Chip)! I had figured how to stop it with user intervention but
not how to automate it; MS documentation was not clear about the need to
specify the exact time.
--
- K Dales


"Tom Ogilvy" wrote:

http://www.cpearson.com/Excel/ontime.htm

has the solution.

--
Regards,
Tom Ogilvy

"K Dales" wrote in message
...
Just found a problem with the code if you try to close the workbook: the
OnTime method will reload the workbook. Not sure yet of the best
solution,
but if I find one will let you know.
--
- K Dales


"K Dales" wrote:

Put this in the Workbook_Open() event procedu
Private Sub Workbook_Open()

TimeOpened = Now()
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

and then insert a module and add this code:
Public TimeOpened As Date

Public Sub Ticker()

Workbooks("BookName").Worksheets("Sheet1").Range(" A1") = Now -
TimeOpened
Application.OnTime Now + TimeValue("00:00:01"), "Ticker"

End Sub

A few notes:
- The display of the date will "freeze" if you are editing a cell's
contents
but will pick up with the correct time when you are done editing
- Since this runs every second it could be a nuisance and take up
processing
time from other tasks - it could be adjusted to update less frequently,
if
desired
- If you are not familiar with how to use event procedures, search this
forum on that topic
--
- K Dales


"Soniya" wrote:

how can i get a time ticker running in a cell?

like how much time I am in the current worksheet.

If I can have a stop watch in cell A1 from 00:00:00 ?

Thanks