Multiple Countdown/Timer

[sdheckler]

I would like to create a timer that meets the following needs:

• Counts from zero to one minute
• At one minute, displays "Limit Reached - Begin Overtime"
• Continues to count upward but pulls additional time from limited pool of 20 minutes
• When limited pool is exhausted, displays "Overtime Limit Reached"
• Reset function available to reset zero to one minute, but NOT overtime pool
• Can be repeated for multiple objects/people

To help explain how the timer will be used, consider a chess game. Each person only has one minute to make a move. Each person also has a pool of 20 additional minutes that can be spent when needed. Thus, a person can take 2 minutes to make a move and be left with 19 in the pool. That person gets the first minute free each turn, which is why the reset function is needed, but the 20 minute pool will only ever decrease. I'd also like to be able to asjust the 1 minute and 20 minute options to make them larger or smaller, so creating a box that the code pulls from would be ideal.

Finally, I'll need to make eight separate instances of this code for eight separate people. Each pool must be independent of the other pools. I'll need to be able to make one person have a 20 minute pool, another have a 40 minute pool, another have 28 minutes, and so on.

Here's what I've done so far using. I right-clicked on a cell and changed format to mm:ss and used this as the set cells. I then put "1:00" and "20:00" into them for 1 minute and 20 minutes, respectively. I then opened the VBA and created a module on that sheet. This is the code so far:

Issues so far:

• Code does not function as intended: no display messages, pool does not count down
• No Start/Stop/Rest Buttons
• No multiple clocks

Any help that could be given is greatly appreciated. I'm fairly good at programming (mostly MATLAB), but VBA is entirely new to me so I don't know what commands/options are available. I'm also on a bit of a time crunch and can't spend a lot of time trying to figure it out myself. Thanks in advance!

[ducky_yeng]

Hi sdheckler

I had a quick try of your code and have made minor updates as a first pass to perform the count up and down. Tried and tested on Excel 2010. Observed count up to 00:01:00 then the 00:20:00 count down to 00:00:00.

Please Login or Register  to view this content.

I could only get the counting to work when the cells were formatted hh:mm:ss and not mm:ss.
I have also used debug.print rather than Msgbox as to stop it from holding up the thread (if that is the technical way of putting it). Unless you click the message box the timer will be held up.
I have a quick look at the rest of the requirements a little later. Hope this helps you to continue with your project.

[sdheckler]

Thanks, ducky. I ran the code you supplied and noticed that I never saw the debug messages in the Spreadsheet. My understanding is that I must have the VBA open with the Immediate Window visible. Is there a way that I could just run the code from Excel, or do I need to be in VBA?

[ducky_yeng]

To run the code from Excel rather than the VBA window, the quick solution would be to add a command button to the sheet that will call this code.
Debug.Print only appears in the immediate window in VBA. The quick solution is to write the text to a specific cell (e.g. D2 - a status cell)
Is this speed chess?

[sdheckler]

I see. I'll work on getting that figured out.

As far as speed chess, it is and it isn't. It's similar to chess but allows for multiple people to play at once. It's a made-up game some friends of mine and I are crafting, and we realized that some sort of timer needs to be available that can easily keep track of things like this; it's supposed to be very fast-paced. If the timer works out, we'll start on a more substantial system rather than an Excel macro. But for now, Excel is cheap and (relatively) easy.

[sdheckler]

the quick solution would be to add a command button to the sheet that will call this code.

I have added two command buttons. I was hoping to make one start the code on single click and reset the 1 minute clock on double click. This has been achieved with two issues: 1) each single click starts another instance of Countup, which severely skews the time scale on numerous clicks and 2) a double click first acts as a single click, thus compounding issue (1). What is the command to stop a running macro in code? I have solved the double-click issue temporarily by swapping the functions (single to reset, double to start).

For button two, I want to stop program on single click (this will be resolved as solution above is presented) and reset the 20 minute countdown on double click (already solved). Again, these were swapped to prevent double/single click issue.

Please Login or Register  to view this content.

I believe once these button issues are in line I will be able to duplicate the code for different cells and claim the issue overall is solved. Very exciting!

One final thought, very small. Is it possible to call a sound when the messages appear?

[ducky_yeng]

Resolved the button issue by creating two buttons as follows:

• Toggle Button - to toggle between start and stop
• Button - to reset the clock and start-stop button

The code for the start-stop toggle button is as follows:

Please Login or Register  to view this content.

The code for the reset button is as follows:

Please Login or Register  to view this content.

A procedure is required and the code is as follows:

Please Login or Register  to view this content.

To play the sound I got the code from http://www.cpearson.com/excel/PlaySound.aspx and called this method to play a system wav file asynchronously whenever the status is updated from within the Realcount procedure.

Hope this addresses your last reply.
If this is going to be a quick prototype / mock up then it should work if you duplicate your controls and code. However, I do not believe that this is the best design if it is to be maintainable and flexible.
I have yet to look at the configuration side of things (e.g. specify the time allowed for the move and pool time for each instance) as currently the code is hard-coded to 1 minute (move time) and 20 minutes (pool time). I shall leave you to investigate this further.

[sdheckler]

Ducky, I want to thank you for helping me with this. I've never coded in Excel and I imagine it would have taken me weeks to learn the proper commands to do what I want to do. This has been a very informative learning opportunity for me, and I just wanted to thank you.

The code looks almost finished. The buttons all work as desired, the messages appear when desired, and I've even got the sound to work just as planned. A minor inconvenience with the sound is that he clock stops running as it plays, but since all the sounds files are one or two seconds longs I'm perfectly willing to make an exception. Everything is looking good so far. The only thing that remains is to incorporate multiple time pools.

My goal was to use the radio buttons to tell the program which time pool to tick down. I tried to simply set the 'extra' variable when the radio is selected, but this does not seem to be how Excel works. It's difficult for me to grasp the sheet/module code and decide which is run before the other or how they interact, or if that is even the issue. Of course, proper command words evade me as well. At any rate, I've attached my Excel file so you can simply look at what I'm trying rather than have me attempt to explain it.

[ducky_yeng]

Sorry about the delay in response, just from vacation. Do you still want me to have a look at it?

[sdheckler]

I hope you enjoyed your vacation. If you don't mind, I could use the help. I've been trying several different things and looking online everywhere for a way to get the option buttons to work the way I want them, to no avail. All I've found so far are instructions on how to get an option button to fill an empty cell or help complete a form; nothing spectacularly helpful to me.

[ducky_yeng]

I have made some simple modifications to get it work as required. Basically,

• Remove all procedural variables that define static ranges such as counter and extra. Instead defined them as member variables of Module 1. This way you can write to and read from them from anywhere within the workbook. You could use Named Ranges if preferred, but will let you investigate that.
• Create the Workbook_Open procedure in ThisWorkbook and initialise Sheet1 (e.g. set all the member variables that define the static ranges such as counter, set all option button values to false, set the value and caption of the start/stop button to "start" and false etc)
• For each optionbuttonX_Click() set the extra range.
• Add verification that user has selected a Character when they click the start/stop button

I will provide the code a little later as I have a few questions:

• Is Reset button supposed to reset extra time for ALL Characters?
• What should happen if the clock has reached 1 minute, extra time is being used and another Character is selected? Should the clock reset to to 00:00:00 and allow that Character to have 1 minute to make their move before using their extra time?

[sdheckler]

Wow, that sounds great. You have gone beyond expectation, Ducky. Thank you!

As for your questions, the Reset button should reset all fields. That button will only be used at the start of a new game, when everyone gets a fresh 20 minute pool. There's no reason to let someone reset their pool in the middle of a game.

If time is being used and another character is selected the timer should stop and the clock reset to zero. It should stop the previous character's time from ticking, but also not start the newly selected character's time from ticking. That is, it resets the Time field to zero, stops counting and deactivates the previous Character's Overtime field (if it was being used), activates the selected Character's Overtime field, sets the "Start/Stop" button to read "Start" and awaits the command to begin counting (in this case, clicking "Start"). I hope that makes sense.

[ducky_yeng]

I have incorporated your clarifications and have attached the workbook. Not too many changes so you could possibly do a code diff to see what has been modified to make it work.
The game sounds intriguing. It reminds me of a version of chess where one player competes against numerous players simultaneously, making one move and going the next opponent to make another move and so on. In addition the game is usually played at speed.
Hope this helps.

[sdheckler]

This looks amazing. Thank you very much, Ducky. We've used it in a few trial runs of the game and it's working wonderfully. I'll send you a copy of the rules for the game once we get it fully fleshed out. Thanks again.