Results 1 to 8 of 8

Thread: Excel timer

  1. #1

    Thread Starter
    Registered User
    Join Date
    Nov 2021
    Posts
    2

    Excel timer

    Hello,

    New member, on this forum,
    Im a beginner, and below code got in online, however seeking any help to add the message box to appear once the time has run out. Thank you.

    Public interval As Date
    Sub Start_Timer()

    interval = Now + TimeValue("00:00:01")
    If Range("D29").Value = 0 Then Exit Sub
    Range("D29") = Range("D29") - TimeValue("00:00:00")
    Application.ontime interval, "Start_Timer"
    'MsgBox "Clock has run out"

    End Sub

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Excel timer

    Are you trying to do this in .NET, or in VBA? This looks more like VBA, but you said you got it online, so even if it IS VBA, it may not be what you want.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Registered User
    Join Date
    Nov 2021
    Posts
    2

    Re: Excel timer

    Quote Originally Posted by Shaggy Hiker View Post
    Are you trying to do this in .NET, or in VBA? This looks more like VBA, but you said you got it online, so even if it IS VBA, it may not be what you want.
    it is a VBA, a excel macro tool. Thanks for the reply.

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Excel timer

    Then I've moved the thread to Office Development, where you will get better answers. VBA and VB.NET are pretty different programs.
    My usual boring signature: Nothing

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel timer

    i am guessing that you want the message box when the time some initial value in d29 gets to 0 so the msg should be between testing the value of d9 and exit sub
    like
    Code:
    If Range("D9").Value = 0 Then 
        MsgBox "Clock has run out"
        Exit Sub
    End If
    but for this to work, you would need some value in the countdown time value, currently showing as zero
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Excel timer

    I'm trying (and failing) to wrap my mind around this
    Code:
    Range("D29") = Range("D29") - TimeValue("00:00:00")
    TimeValue("00:00:00")???
    Huh?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

  8. #8
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: Excel timer

    Quote Originally Posted by Zvoni View Post
    I'm trying (and failing) to wrap my mind around this
    Code:
    Range("D29") = Range("D29") - TimeValue("00:00:00")
    TimeValue("00:00:00")???
    Huh?
    Yes that seems odd. Normally for a countdown timer that would be "00:00:01".
    The code you have I would possibly expect might run until midnight, at which time the clock should roll over to "00:00:00" and your check for D29 being 0 would probably be true.
    You would need that line I think to get the D29 set to 0 at midnight result.
    You should see the clock incrementing in D29 rather than decrementing with this implementation.

    p.s. Actually I don't see that D29 is incrementing, so I don't see it stopping at midnight either.
    Perhaps the code was copied incorrectly and it should have been "00:00:01".
    Last edited by passel; Nov 23rd, 2021 at 09:55 AM.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

Posting Permissions

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



Click Here to Expand Forum to Full Width