Consulting

Results 1 to 8 of 8

Thread: Countdown inside a cell with a 1 second delay

  1. #1
    VBAX Regular
    Joined
    Mar 2005
    Posts
    50
    Location

    Countdown inside a cell with a 1 second delay

    Friends,

    How are you all?

    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.

    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.

    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? Now, if you prefer to use any procedure related to time events, that's OK for me. No problem.

    Thank you very much for your attention, guys.

    Hugs for all.

    Bruno

  2. #2
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    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,
    Brandtrock




  3. #3

    Arrow How to pass a second of time doing nothing

    Start = Now
    Do
    Loop Until Now >= Start + TimeSerial(0, 0, 1)
    Last edited by Zack Barresse; 03-11-2005 at 02:37 PM. Reason: Added VBA Tags.

  4. #4
    VBAX Regular
    Joined
    Mar 2005
    Posts
    50
    Location
    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

  5. #5
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Bruno

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

  6. #6
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Quote Originally Posted by Norie
    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.
    Brandtrock




  7. #7
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  8. #8
    VBAX Mentor Brandtrock's Avatar
    Joined
    Jun 2004
    Location
    Titonka, IA
    Posts
    399
    Location
    Quote Originally Posted by 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.
    Not a problem. Here is the link. It wasn't much of a bother.
    Brandtrock




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •