Can you put a running clock into excel?

nightracer

Board Regular
Joined
May 17, 2004
Messages
137
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?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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.
Code:
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
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Disable
End Sub
HTH


Mike
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,013
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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:
Code:
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

Board Regular
Joined
May 17, 2004
Messages
137
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,013
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

Board Regular
Joined
May 17, 2004
Messages
137
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
“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

Board Regular
Joined
May 17, 2004
Messages
137
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

Board Regular
Joined
May 17, 2004
Messages
137
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,539
Messages
5,445,076
Members
405,314
Latest member
ao5835403

This Week's Hot Topics

Top