• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countdown 60 minute timer

Prinz Jaymes

Prinz Jaymes

Member
I want to test my students.
They to read a report from a separate monitor, but then select answers from an Excel worksheet. They will be using data validation to select from a set of multiple choice answers.

My issue is this, is there a way to setup a 60 minute countdown timer using VBA?

I will be adding highlighting a cell (say H2) that shows their progress. So, if 7 out of 10 questions have been answered, it means they have completed 70% of the TESting.

However, I would like the CELL (say H2) with their Progress score to FREEZE or lock if the 60 minutes have elapsed.
 
Here's an example to get you started.
 

Attachments

  • Example Timer.xlsm
    17.1 KB · Views: 43
Hi Ashish,

Thanks for that input. Will attempt it in few hours when I get to the office.
However, still not sure which method does apply to my issue.
"Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed)."

I am looking for a way to have a 60 minute timer that can stop or freeze cells in a specific column(answers) when the 60 minutes elapse.
 
Hi Prinz,

I think we need to know more about how you are currently changing the cell, and what "freezing" it means. If user is the one editing the cells, then our countdown timer should fire off a macro that locks the cells. If you have a macro somewhere that is being triggered by a change event (like in my example), then you can setup a toggle like I showed, that the change_event checks for. If however, you have a continuously running macro, we will need to change something. VB does not support multi-thread running (can't run two macro simultaneously). The count down timer's macro would be put in the queue to run, but wouldn't actually run until after your currently running macro finished (which may be too late).
 
Hi Luke
I want to keep as simple as possible.
I created similar timer as the one in the link below.

http://chandoo.org/wp/category/vba-macros/
(Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study])

I like the idea of a Timestamp to show on the Excel testing sheet, which I have done.
The Durations part is set at [mm:ss].
However
1. Any way to create some sort of VBA to stop timer when Durations reaches [60:00]?
2. It would be nice for the person testing to see the countdown.

Any ideas?
 
1. The linked example will not work in your case. It's a passive timer, meaning it's depending on input from user to calculate duration. You want to define duration, and have that trigger something. Again, going back to my example, you can set it for any time. I used 10 seconds (0:00:10) simply for demonstration, but you could easily set it to 1 hour (1:00:00).

2. To do this via a macro would be bad. Would require code constantly getting called, making response time slow (not what you need in a time test). You could have the first macro put time limit in a cell, and then another calculation to show time left. Like:
Code: 
Range("A1").Value = Now + TimeValue("1:00:00")
Range("A2").Formula = "=""Time left: ""&TEXT(MAX(0,A1-NOW()),""mm:ss"")"

As user makes changes in XL, this would trigger a natural recalculation, and timer would update. Wouldn't be truly "live", but would be far less calculation intensive.
 
Here's a modification to file above, showing timer.
 

Attachments

  • Example Timer2.xlsm
    20.4 KB · Views: 50
You must log in or register to reply here.
Back
Top