Countdown timer

Posted by Philip L. Carnahan on September 12, 2001 4:54 PM

I built a spreadsheet for some 3rd graders help them practice for their in school multiplication tests. I have 30 problems that they need to complete in 3 minutes. I want to spiff it up by adding a Countdown Timer in one cell with a 'button' below it to commence the test? Then I will add a 'macro' to unhide the correct answers, compare their answers, and give them a % grade with maybe a link to an 'ice cream parlor' as a reward. Maybe even a bell when the 3 min. is up!!
Thanks for your help.

Posted by Damon Ostrander on September 14, 2001 11:38 PM

Hi Phil,

Here's some code for a countdown timer that you might find useful. The first routine is click event code for a button named btnStart that resides on the worksheet of interest (you must create this button).

Private Sub btnStart_Click()
nSecs = 1
StartTime = Time()
EndTime = StartTime + 180 / 86400#
CountDownB4
End Sub

This second block of code must be put into a macro module in the workbook.

Public nSecs As Long
Public StartTime As Date
Public EndTime As Date

Sub CountDownB4()

' This procedure demonstrates the creation of a countdown
' timer in cell B4 on the sheet associated with this event
' code pane. Each time this routine is called it schedules
' itself one second in the future

Const Sec = 1 / 86400#
Dim NextTime As Date

If nSecs &LT;= 180 Then '3 minutes
NextTime = StartTime + Sec * nSecs
Application.OnTime NextTime, "CountDownB4"
'write time into cell B4 as a string
[B4] = Format(Abs(EndTime - NextTime), " n:ss")
nSecs = nSecs + 1
Else
Beep
MsgBox "Time is up", vbExclamation
End If

End Sub

Hopefully this will at least give you a template for how to proceed.

Happy teaching.

Damon

PS. To make the above code more ironclad, and since it is operating asynchronously, you should add a check to make sure when CountDownB4 reschedules itself 1 second in the future, that the time has not already passed due to being delayed by other processing.