Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Can you put a running clock into excel?

This is a discussion on Can you put a running clock into excel? within the Excel Questions forums, part of the Question Forums category; Hi all I have a process that is due to start at say 3.00pm and I need to be able ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    90

    Default Can you put a running clock into excel?

    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?

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Can you put a running clock into excel?

    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

  3. #3
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39° 17' 15" N, -94° 40' 26" W
    Posts
    9,770

    Default Re: Can you put a running clock into excel?

    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
    Greg
    ………………………………………………
    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Can you put a running clock into excel?

    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

  5. #5
    Board Regular
    Join Date
    May 2004
    Posts
    90

    Default

    Thanks Mike/Greg

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

  6. #6
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39° 17' 15" N, -94° 40' 26" W
    Posts
    9,770

    Default Re: Can you put a running clock into excel?

    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
    Greg
    ………………………………………………
    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  7. #7
    Board Regular
    Join Date
    May 2004
    Posts
    90

    Default

    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?

  8. #8
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Can you put a running clock into excel?

    “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

  9. #9
    Board Regular
    Join Date
    May 2004
    Posts
    90

    Default

    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?

  10. #10
    Board Regular
    Join Date
    May 2004
    Posts
    90

    Default

    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com