Can you put a running clock into excel?

[nightracer]

Hi all

I have a process that is due to start at say 3.00pm and I need to be able to show the elapsed time that the process has been running.

I've used the formula =NOW()-"15:00:00" which gives me the answer but it only updates when you hit return or enter text.

Is there a way to display a running clock/stopwatch?

 

[earthworm]

Nightracer,

Your other code is failing because you don't have an "End Sub" statement before you're calling another Sub statement. You cannot have a Sub or Function statement inside another Sub or Function statement.

I altered Mike's very nice starting point a bit. This would give you the elapsed time in cell C4. In my test wb, I named had three buttons named, btnResetTimer, btnStartTimer and btnStopTimer.

In a Standard module

Dim SchedRecalc As Date, datStartTime As Date

Sub RecalcTimer()
****Dim wbk As Workbook
****Dim ws As Worksheet
****
****Set wbk = ThisWorkbook
****Set ws = wbk.Sheets(1)**' <== Change the "1" to appropriate #
****
****'ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
****ws.Range("C4").Value = Format(Now - datStartTime, "hh:mm:ss")
****
****Call SetTimer
End Sub

Sub SetTimer()
****If datStartTime = 0 Then datStartTime = Now
****SchedRecalc = Now + TimeValue("00:00:01")
****Application.OnTime SchedRecalc, "RecalcTimer"
End Sub

Sub StopTimer()
****On Error Resume Next
****Application.OnTime EarliestTime:=SchedRecalc, _
********************** Procedure:="RecalcTimer", _
********************** Schedule:=False
End Sub

Sub ResetTimer()
****datStartTime = 0
End Sub


In the Worksheet module for the worksheet with the buttons.

Private Sub btnResetTimer_Click()
****ResetTimer
End Sub

Private Sub btnStartTimer_Click()
****SetTimer
End Sub

Private Sub btnStopTimer_Click()
****StopTimer
End Sub


HTH

Please share the sample excel , i m completely lost .

 

[toe-knee]

how do you go about all of these? where do i put these codes? i'm lost.
i need a stopwatch that would run even if i'm typing on the same sheet. something that has a start/stop button...

 

[cpg84]

how do you go about all of these? where do i put these codes? i'm lost.
i need a stopwatch that would run even if i'm typing on the same sheet. something that has a start/stop button...

How about this?:

https://spreadsheets.google.com/ccc?key=0ApBRM_Gi8OxldGEtd1J3SjJ1VWoxN2d0X1hyNlBrN2c&hl=en

 

[earthworm]

How about this?:

https://spreadsheets.google.com/ccc?key=0ApBRM_Gi8OxldGEtd1J3SjJ1VWoxN2d0X1hyNlBrN2c&hl=en

???

 

[toe-knee]

the stopwatch is not working for me. everytime i try to type something into the same sheet, it stops. i tried to insert a power point presentation that functions as stop watch. but everytime i try to run the show, it maximizes to full screen. i want it to run in just a portion of the excel file. so i can see the timer while i'm typing something into the same sheet. is this possible?

 

[niko003]

is it psible to get clocks hh: mm: ss each in diferent cell? how?

 

[Jivex]

Macro for running clock in excel

Sub Macro1()

ActiveSheet.Calculate
Application.OnTime Now + TimeValue("00:00:01"), "Macro1"

End Sub

 

[Jivex]

OR:

Sub Macro1()

Range("A1").Calculate
Application.OnTime Now + TimeValue("00:00:01"), "Macro1"

End Sub

 

[farewinners]

I used this code and it worked well yesterday. Today it runs for a few seconds and then I get an error message "Code execution has been interrupted". When I hit the Debug button it takes me to the VBA and "Sub Recalc()" is highlighted in yellow. Sometimes it Highlights "Call SetTime" I have Cell H3 =Second(C4) and cell H5 runs an if statement based on the number in cell H3.


Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet


Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")




ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")


Call SetTime
End Sub


Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:03")
Application.OnTime SchedRecalc, "Recalc"
End Sub

 

[Jivex]

I used this code and it worked well yesterday. Today it runs for a few seconds and then I get an error message "Code execution has been interrupted". When I hit the Debug button it takes me to the VBA and "Sub Recalc()" is highlighted in yellow. Sometimes it Highlights "Call SetTime" I have Cell H3 =Second(C4) and cell H5 runs an if statement based on the number in cell H3. Dim SchedRecalc As Date Sub Recalc() Dim wbk As Workbook Dim ws As Worksheet Set wbk = ThisWorkbook Set ws = wbk.Sheets("Sheet1") ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM") Call SetTime End Sub Sub SetTime() SchedRecalc = Now + TimeValue("00:00:03") Application.OnTime SchedRecalc, "Recalc" End Sub

"SetTime" instead of "Recalc"