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?

 

[Ekim]

The following macros will place today’s date and a running time in cells C3 and C4 respectively. The last macro manually stops the clock.

Standard module.

Dim SchedRecalc As Date
Sub Recalc()
Range("C3").Value = Format(Now, "dd-mmm-yy")
Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub

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

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Disable
End Sub

HTH


Mike

 

[Greg Truby]

For clocks:

http://www.cpearson.com/excel/download.htm
http://www.j-walk.com/ss/excel/files/general.htm

Search the code in them for the term "OnTime". (Or search here for that term.)

If you are running a loop in a VB procedure and simply want to periodically update the user, you can simply use Application.Statusbar = xxx. You might search for the term "Progress Indicator" here for more ideas.

{Edit}I see that Mike beat me to the punch - and with a very nice answer.{EndEdit}

HTH

 

[Ekim]

I just realized that unless the “ReCalc” macro explicitly refers to the active workbook, the date and time is place in all open workbooks. So, change the first macro to the following:

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

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

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub

Regards,

Mike

 

[nightracer]

Thanks Mike/Greg

Can these be used with formula's in any way to show elapsed time rather than just time of day?

 

[Greg Truby]

Sure, using Mike's great example - go to cell B4 and hit Ctrl+: to enter the current time. Then go to D4 and put in the formula =C4-B4 and format it to hh:mm:ss

 

[nightracer]

Mike, thanks again

When I try to paste your macro into the vb and run it I get the message 'can't execute code in break mode'.

Any ideas what this might mean?

 

[Ekim]

“can't execute code in break mode” normally means that you have attempted to run a macro that has previously stop running because of some error. To reset the macro:

In the VBA editor:
Go to the menu item “Run”
Click “Reset”

If the macro does not run on your system, what error message is displayed?

Regards,

Mike

 

[nightracer]

I am trying to run this from a form button, but am a newbie to macros so please forgive any novicy mistakes!

Here are the steps I've taken:

1 Insert a form button onto the worksheet
2 in Assign Macro I've clicked 'new'
3 In visual basic i've copied in the above to show:

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

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

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub

4 I've gone back to the worksheet and clicked the button, but get the message:

Compile Error: Expected End Sub with 'Dim' highlighted on the second line: Dim SchedRecalc As Date

Anyone know what I've done wrong? Do I need to use different settings?

 

[nightracer]

Thanks everyone, now working with:

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

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

ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")

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

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub

Thanks again
nightracer