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?

 

[Greg Truby]

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

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> SchedRecalc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>, datStartTime <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>

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

<SPAN style="color:#00007F">Sub</SPAN> SetTimer()
    <SPAN style="color:#00007F">If</SPAN> datStartTime = 0 <SPAN style="color:#00007F">Then</SPAN> datStartTime = Now
    SchedRecalc = Now + TimeValue("00:00:01")
    Application.OnTime SchedRecalc, "RecalcTimer"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> StopTimer()
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    Application.OnTime EarliestTime:=SchedRecalc, _
                       Procedure:="RecalcTimer", _
                       Schedule:=<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ResetTimer()
    datStartTime = 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

In the Worksheet module for the worksheet with the buttons.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> btnResetTimer_Click()
    ResetTimer
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> btnStartTimer_Click()
    SetTimer
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> btnStopTimer_Click()
    StopTimer
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH

 

[Ekim]

Greg,

Very nice. The routine is now operating like a real stop watch.

In my routine, a problem arises if you programmatically set the current time and compare that with the running time. In this case, the “Recalc” macro sets both times running. This was over come by putting the current time in a separate worksheet and subtracting the time in C4, sheet1, with the current time. This is now academic.

Hopefully the OP understands that with your routine he has to place 3 buttons from the Toolbox menu on sheet 1 and rename them btnResetTimer , btnStartTimer and btnStopTimer.

Regards,

Mike

 

[nightracer]

Thanks guys, have just forund this extension to the response.

Greg/Ekim - as a newbie to VBA i can't seem to get this to work, is there any chance of a step by step guide on what I need to put where and what buttons I need to get and how to link them?

Thanks
Chris

 

[Ekim]

Chris,

Would it be easier if I just sent you a workbook? Check your Private Messages – I have included my email address.

Regards,

Mike

 

[countryfan_nt]

This is really cool. But the date starts when clicking a button. Is there a way to have to have the timer run automatically whn I open the workbook. Instead of clicking a button.

Thanks Guys Great stuff.

 

[PA HS Teacher]

Here's a stop watch I found back in september. In the last post I changed it so that it records the overall time and the split onto the spsreadsheet, in addition to updating a running clock in cell A1.

You would have to add four buttons to call the four subs. Start, Stop, Split, and Reset.

(not sure that this is what you need, but it is kind of cool.)

http://www.mrexcel.com/board2/viewtopic.php?t=105198&start=10

 

[Greg Truby]

Is there a way to have to have the timer run automatically whn I open the workbook. Instead of clicking a button.

Not tested, but I think it would be as simple as:

Private Sub Workbook_Open()
    SetTimer
End Sub

 

[countryfan_nt]

Cool thanks, OK what if I have two buttons with two different functions. and I want to run once the workbook opens.

 

[Greg Truby]

Same deal. You can call whichever routines you need to from the open method:

Private Sub Workbook_Open()
    myAlarmClock
    myShave
    myShower
    myBreakfast
    If DatePart("w", Date, vbSaturday) > 2 Then
        myCommute
        myWorkday
    Else
        myAvoidHoneyDoList
    End If
End Sub

HTH

 

[earthworm]

Same deal. You can call whichever routines you need to from the open method:

Private Sub Workbook_Open()
    myAlarmClock
    myShave
    myShower
    myBreakfast
    If DatePart("w", Date, vbSaturday) > 2 Then
        myCommute
        myWorkday
    Else
        myAvoidHoneyDoList
    End If
End Sub

HTH

its not working its showing syntax error

please share the excel

i dont want to relate the code with the problem .

i want the code which can work on new excel please help