Countdown inside a cell with a 1 second delay

[brunces]

Friends,

How are you all? http://www.excelforum.com/images/smilies/smile.gif

I've created a quiz game (questions and answers for my pupils) in Excel and now there's only one little detail that I just don't know how to do. I need a COUNTDOWN! I've seen it once, but I didn't see the codes.

When I ask something to a player, this person has 10 seconds to answer the question. If he answers during the countdown, I stop the counter. If not, the counter hits "zero" and the text "Time up!" appears. That's it! Simple!

So, here's what I need...

1) A button to start the counter;
2) A code which refreshes a cell (A1, for example) every 1 second, from 10 to "zero" (a loop with A1 - 1, I guess);
3) The text "Time up!" (in A1) after hitting "zero";
4) A button to stop the counter anytime I want to, during the countdown.

My sheet and cell are ready. The buttons (StartCounter and StopCounter) are already created too. Now, I just need the code. http://www.excelforum.com/images/smilies/smile.gif

Note 1:
The button StartCounter lies in the main sheet. When I press this button, it takes me to another sheet (named counter) and starts the countdown. In the counter sheet lies the button StopCounter. When I press this button, it stops the counter and takes me back to the main sheet. If you want to show me two codes, one for each button, that's OK (I prefer so). If you show me one single code for both buttons, that's OK too. It's up to you. http://www.excelforum.com/images/smilies/smile.gif

Note 2:
I've searched for a countdown template on the internet, but all I got was "date and time templates". I don't need a countdown timer like that, for I won't use specific "date or time". I just need a code to subtract 1 from any number I decide, with a 1 second delay. It's a timer, offcourse, but it's nothing formated to "time". It's just a kind of loop like "CellX = CellX - 1" with a 1 second delay. Right? http://www.excelforum.com/images/smilies/smile.gif Now, if you prefer to use any procedure related to time events, that's OK for me. No problem. http://www.excelforum.com/images/smilies/smile.gif

Thank you very much for your attention, guys.

Hugs for all. http://www.excelforum.com/images/smilies/smile.gif

Bruno

[Brandtrock]

Here is a timer routine I use. In your start button run the sub StartTimer and in your stop button code run the sub StopTimer. This code goes in a standard module. Name the cell that your alloted time will go in "TimeDelay".



Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1
Public Const cRunWhat = "Counter"
Public delay As Date

Sub StartTimer()
' start the procedure here
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True
End Sub

Sub StopTimer()
' OnTime needs to be switched off
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False
End Sub

Sub Counter()
' perform the countdown
delay = Range("TimeDelay")
'the starting value
If delay = 0 Then
StopTimer
MsgBox ("Time's Up!")
Exit Sub
End If
delay = delay - TimeSerial(0, 0, 1)
Range("TimeDelay") = delay
StartTimer ' schedule timer
End Sub


Hope this helps,

[QAQ]

Start = Now
Do
Loop Until Now >= Start + TimeSerial(0, 0, 1)

[brunces]

Guys,

Here's a code I just created.



Sub StartCounter()
Range("A1").Value = 10
While Range("A1").Value >= 0
Application.Wait (Now + TimeValue("0:00:01"))
Range("A1").Value = Range("A1") - 1
Wend
Range("A1").Value = "Time up!"
End Sub


It works perfectly! But there's one problem... I just can't find a way to STOP the countdown while it's running, for Excel suspends all its activities, therefore I'm not able to click any button or type anything (I guess... Maybe I'm wrong!).

Anybody knows how a solution fot that?

I've thought about a code like this...



Sub StartCounter()
On Application.SendKeys ("{enter}") GoTo ExitCounter
Range("A1").Value = 10
While Range("A1").Value >= 0
Application.Wait (Now + TimeValue("0:00:01"))
Range("A1").Value = Range("A1") - 1
Wend
Range("A1").Value = "Time up!"
ExitCounter:
End Sub


It doesn't work, I know! But maybe someone knows another way to do something similar to the example above. How could I stop the running process pressing a key? Is possible (or not, because of that WAIT procedure)? Or how could I enable the click of a button (during the process) and tell Excel that "when I click this button, it must stop the countdown"?

That's it, guys. :)

Thank you very much for your attention.

Hugs for all.

Bruno

[Norie]

Bruno

Check my post on
http://www.ozgrid.com

[Brandtrock]

Bruno

Check my post on
http://www.ozgrid.com

Your link goes to the home page, not a post Norie. Just thought I'd let ya know.

[Norie]

Brandtrock

I know - I couldn't be bothered going to Ozgrid, finding the post, copying the link to the post and then pasting it in.

Plus I'm not 100% sure of the syntax for hyperlinks on this board.

Hopefully Bruno will be able to find it himself.

[Brandtrock]

Brandtrock

I know - I couldn't be bothered going to Ozgrid, finding the post, copying the link to the post and then pasting it in.

Plus I'm not 100% sure of the syntax for hyperlinks on this board.

Hopefully Bruno will be able to find it himself.

Not a problem. Here is the link (http://www.ozgrid.com/forum/showthread.php?t=31089). It wasn't much of a bother. :hi: