Countdown Timer

[rickyckc]

Hi All,

Can someone please show me how to perform this:

I add hrs:mins:sec on cell A1, example 49:38:23

Based on the date and time, I've entered A1, cell C1 will show me the date and time when A1 is countdown to zero.

Thanks in advance.

Best Regards,
Ricky

 

[Datsmart]

Put this formula in cell C1.

=NOW()+A1

Be sure to format C1 to Date and Time.

 

[rickyckc]

Thanks John. What if I also want A1 to start counting down once I hit 'Enter' ?

 

[Datsmart]

Running code to count down until cell A1 hits zero will tie up your computer for that amount of time, not something I would recommend for 49 + hours.

The following code will do what you ask, but again, I would only do it for very short times.

Sub CountdownTest()
'"Ctrl+BREAK" to stop!
'Set variable using Range A1
howLong = Cells(1, 1).Value
myNext:
    Start = Timer
        Do While (Timer < Start + 1 And howLong <> 0)
            myAddr = Cells(1, 1).Address
            DoEvents
            Range("A1").Value = Hour(howLong) & ":" & Minute(howLong) & ":" & Second(howLong)
        Loop
    howLong = (howLong - TimeSerial(0, 0, 1))
    If howLong <= 0 Then GoTo myEnd
    GoTo myNext
myEnd:
End Sub

This could be set to run on "Enter" but that would not allow you to do anything on that worksheet but start the macro.

 

[rickyckc]

Noted and thanks for your time.

 

[chuckchuckit]

This count down timer will not lock up computer while in use, as it launches a new macro "only after" the elapsed time.

Test this by putting 00:00:05 in cell A1 to add 5 seconds, or any length of time you wish. It is using format of hh:mm:ss

Sub CountDownTimer()
    Dim TimeWeEnd 
    TimeWeEnd = Now() + TimeValue(Range("A1").Text)
    Application.OnTime TimeValue(TimeWeEnd), "DisplayDoneMessage" 
End Sub
 
Function DisplayDoneMessage()
    Range("C1").Value = "Done!"
End Function

 

[chuckchuckit]

To display the count down time left in cell A1, you could also write an other sub that keeps calling itself every few seconds or so to update the A1 screen time left.

If you use the Application.OnTime call to do so, you again will not be tying up the computer even if hours elapse while counting down. You can just keep modifying the TimeValue to the call each time it is called to do the next update. Just add the number of seconds to the Now() value till your next desired call.

This will also not tie up the computer, as each Application.OnTime call should not intefere with any other functions running.

I just did this type of code for the first time yesterday and I have 3 separate Excel instances that have such countdown code running and updating the screen, while each instance is doing queries and calculations also. I can also copy data from cells in the screen to paste to other screens etc, while all is running too. No problems encountered.

Chuck

 

[rickyckc]

Hi Chuck,

Thanks for your code and time.

Best Regards,
Ricky

 

[eyong123]

is it possble to hav a message box projecting the count down
cheers

 

[chuckchuckit]

Msgbox requires a user input and halts all VBA code until user does something.

But you can use a Userform if you use the "vbModeless" with it. As then it pops up a box similar to a Msgbox but the "vbModeless" parameter will allow code to continue to run until you give a command to close the box.

"UserForm1.Show vbModeless" would be such a command. Learning Userforms video's on YouTube. 1st of 3 parts:

http://www.youtube.com/watch?v=5PN7lWJSobQ