Results 1 to 5 of 5

Thread: Stopping count-up timer code after a certain number of seconds

  1. #1
    Neophyte amerillo's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    2016

    Stopping count-up timer code after a certain number of seconds



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    Just wondering if someone can help me with what I think is a very small coding problem that I can't seem to figure out for myself! VBA is certainly not my forte, but I have managed to come up with the attached code for a count-up timer. Currently the timer continues indefinitely, however, I would like it to stop at 65 seconds. Could someone please tell me what coding line(s) I would need to add to make this happen? I have tried various work arounds, and they have failed to do the trick! I'm guessing it is some kind of "Do Until" loop that I need to enter?

    Many thanks!
    AM

    Code:

    I have this code written into a module:

    Code:
    Dim CountDown As Date
    Dim count As Range
    
    
    Sub RunTime()
    CountDown = Now + TimeValue("00:00:01")
    If count.Value >= TimeValue("00:00:01") Then
       Application.OnTime CountDown, "Counter"
    Else
       Call DisableCount
    End If
    End Sub
    
    
    Sub Counter()
    Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Data1")
    Set count = ws.Range("b11")
    count.Value = count.Value + TimeValue("00:00:01")
    Call RunTime
    End Sub
    
    
    Sub DisableCount()
    On Error Resume Next
    Application.OnTime EarliestTime:=CountDown, Procedure:="Counter", Schedule:=False
    End Sub
     
    Sub Reset()
    
    
    Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Data1")
    Set count = ws.Range("b11")
    On Error Resume Next
    Application.OnTime EarliestTime:=CountDown, Procedure:="Counter", Schedule:=False
    count.Value = TimeValue("00:00:00")
    End Sub

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    401
    Articles
    0
    Excel Version
    Excel 2016
    When posting code please remember to wrap your code in code tags either start your code like this [code] and end like this [/code] or highlight your entire code and click the # in the post window, it makes it easier for folk to read and not have to scroll a big post
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    Untested, try changing:
    If count.Value >= TimeValue("00:00:01") Then
    to:
    If count.Value <= TimeValue("00:01:05") Then

  4. #4
    Neophyte amerillo's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Simon Lloyd View Post
    When posting code please remember to wrap your code in code tags either start your code like this [code] and end like this [/code] or highlight your entire code and click the # in the post window, it makes it easier for folk to read and not have to scroll a big post
    Sure no worries, sorry!!

  5. #5
    Neophyte amerillo's Avatar
    Join Date
    Oct 2018
    Posts
    3
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    Untested, try changing:
    If count.Value >= TimeValue("00:00:01") Then
    to:
    If count.Value <= TimeValue("00:01:05") Then
    That seems to work! So simple! Thank you so much! I've spent ages trying to work around this with no success!

Posting Permissions

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