Results 1 to 7 of 7

Thread: VBA countdown timer
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA countdown timer

    Hello,

    I want to make a countdown timer with counting to a date in the future in format "dd hh:mm:ss" or similar. F.e. how many days, hours, minutes and seconds left till "21/10/2019 15:00:00". I have newly found the timer function, but can not make it count backwards.

    All help and ideas appreciated. Thanks.

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA countdown timer

    Hi, maybe you could consider using formulas:

    Excel 2013/2016
    ABCDEF
    1StartEndDaysHoursMinutesSeconds
    229/08/2019 09:38:2221/10/2019 15:00:005352138

    Sheet1



    Worksheet Formulas
    CellFormula
    A2=NOW()
    C2=INT(B2-A2)
    D2=HOUR(MOD(B2-A2,1))
    E2=MINUTE(MOD(B2-A2,1))
    F2=SECOND(MOD(B2-A2,1))

    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA countdown timer

    Hi FormR,
    Thank you for your suggestion, it was the first thing i tried. The thing is i am trying to make this as active clock timer, but the formula for current time does not refresh despite formula calculation option is set to automatic.
    Have an idea how to sort this out, maybe even force calculations with VBA?
    Regards.

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA countdown timer

    Hi, you can force Excel to recalculate by pressing F9.

    I suppose you could use On Timer in VBA to schedule a macro to do the re-calculation periodically.
    [code]your code[/code]

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,810
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA countdown timer

    .
    Code:
    Option Explicit
    
    
    Public bTimerOn As Boolean
    
    
    Sub ToggleTimer() ' This Sub will switch the automatic timer on if it was off before, and vice-versa
    bTimerOn = Not bTimerOn
    
    
    Refresh ' It calls the Refresh Sub
    End Sub
    
    
    Sub Refresh() ' This Sub calculates the sheet and tells Excel to call it again after 1 second
    Application.Calculate
    
    
    If bTimerOn Then ' If bTimerOn is True, start refreshing automatically
    Application.OnTime Now + TimeValue("00:00:01"), "Refresh" ' The intervall is set to 1 second but can be changed variably
    End If
    End Sub

    Download workbook : https://www.amazon.com/clouddrive/sh...TXebd5aHmuo32a

  6. #6
    New Member
    Join Date
    Feb 2019
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA countdown timer

    Thank you Logit,

    I have used some freestyle and ended up with the below code, that currently works fine.
    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
               
    Dim CurrentTime
    Dim i As Long
    Dim j As Long
            
    Worksheets(1).Range("B4").Value = 0
    j = Worksheets(1).Range("B3").Value
                            
        For i = 1 To j
                    
            CurrentTime = Timer
                    
            Do While Timer < CurrentTime + 1
                    
            DoEvents
                
            Loop
                                    
            Worksheets(1).Range("B4").Value = i
                    
        Next i
                        
    End Sub
    with B3 being the sum of time left in seconds.
    Regards

  7. #7
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,810
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA countdown timer

    .
    Sorry I misunderstood. Your thread title said "Countdown Timer".

Some videos you may like

User Tag List

Tags for this Thread

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
  •