VBA countdown timer

wasntme

New Member
Joined
Feb 1, 2019
Messages
37
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Hi, maybe you could consider using formulas:

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style=";">Days</td><td style=";">Hours</td><td style=";">Minutes</td><td style=";">Seconds</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">29/08/2019 09:38:22</td><td style="text-align: right;;">21/10/2019 15:00:00</td><td style="text-align: right;;">53</td><td style="text-align: right;;">5</td><td style="text-align: right;;">21</td><td style="text-align: right;;">38</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=NOW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INT(<font color="Blue">B2-A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=HOUR(<font color="Blue">MOD(<font color="Red">B2-A2,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=MINUTE(<font color="Blue">MOD(<font color="Red">B2-A2,1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=SECOND(<font color="Blue">MOD(<font color="Red">B2-A2,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

wasntme

New Member
Joined
Feb 1, 2019
Messages
37
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.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
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/share/Do210jMhUDUD4xutTmXZHLpjtVo1ATXebd5aHmuo32a
 

wasntme

New Member
Joined
Feb 1, 2019
Messages
37
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
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
Sorry I misunderstood. Your thread title said "Countdown Timer".
 

Forum statistics

Threads
1,082,275
Messages
5,364,181
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top