MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countdown Timer.


Posted by Jason Hendry on October 22, 2001 11:41 AM

Anyone have the code for a timer? Looking for a Digital timer where I could enter the duration in a cell and hit a button to start the countdown. Fairly certain that it is possible as I've run into a few clocks while scavenging the web.

On a side note, where might I learn this stuff so I can stop asking questions and perhaps contribute to the answers someday?


Posted by Geoffrey Hurst on October 23, 2001 1:26 AM

Put this in the Sheet module :-
Private Sub CommandButton1_Click()
Call Timer
End Sub

Put these in a normal module :-
Dim CountDown As Date
Sub Timer()
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub Reset()
Dim count As Range
Set count = [A1] ' A1 contains the number of seconds for the countdown.
count.Value = count.Value - 1
If count <= 0 Then
MsgBox "Countdown complete."
Exit Sub
End If
Call Timer
End Sub
Sub DisableTimer()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

Put this in the Worbook module :-
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DisableTimer
ActiveWorkbook.Saved = True
End Sub

http://www.mrexcel.com/book.shtml