+ Reply to Thread
Results 1 to 68 of 68

Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

  1. #1
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Smile Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    As a volunteer for a local community gym, using code cobbled from around the forums and Internet, I have a countdown timer for 10 courts in a gym where I need to be able to START STOP and RESET each court.

    I could get it working but since the START triggers a new Macro, after I first START the countdown, clicking on the same START button which calls the timer subroutine results in the time being sped up!! How do I avoid that ? This is my primary and urgent problem.

    I would appreciate help with three things:
    1. Clicking on START button should not do anything if the timer is already running.
    2. I would like to add a PAUSE button but I need a visual indicator that the countdown is paused rather than just the clock which can be hard to read.
    3. I would like the start time of the countdown to be a variable that is picked from an excel cell. (right now its hardcoded at 20 minutes in the VBA code). Ideally I should be able to set a variable countdown start time for each court.

    I am attaching my sheet.
    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Hi, I haven't looked at the file yet but you mention a countdown timer for 10 courts
    keep in mind that Excel is nit a multi thread and than only one process runs and the next macro only starts after the first is finished.
    I'll probably have more questions when I read the file and what you have.
    Do you yourself have any VBA experience? I mean actually writing your own code and being able to analyse it?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Hi Hans, I do not have much VBA experience but I can understand code. I had to cobble it together from code from others VBA examples quickly because the gym had an event where they needed the Timers. I did not realize that Excel was single process! That can be a problem but the delay then would be a maximum of 10seconds which would be tolerable. The Speeding up on every START click is the immediate problem because each click multiplies the speed! I was thinking that if there was a way to hide the START button once the macro starts running, it might do the trick. Any help would be nice.

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Iíve got some ideas I started working on but am away for the day now
    Will continue later evening and let you know

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I think I did a little more than that.
    I made quite some changes as far as the button names goes, this to make it easier to control
    The functionality of the buttons has changed:
    RESET sets the value back to the 20 minutes, and stops that timer as well as displays the START button
    When you press the START button, it is hidden and the former STOP button becomes visible with the text PAUSE
    Click PAUSE and the timer is stopped and the caption is replaced with RESUME, click it and ... the timer resumes.
    Press RESET to start from the beginning and this for all 10 of them.

    The button on the left RESET ALL TIMERS does just that, sets them all back to the 20 minutes and stops all the timers

    You mentioned something about the 20 minutes, but I don't remember but this is it for now.

    My initial Idea was one routine to process all timers but this will do the job.

    The vba Modules are named accordingly Module01 through Module10

    Module00 is the management module where the global variables are set, one for each timer and the worksheet name
    The ranges where the time is shown are all named ranges: TIMER1, TIMER2 etc. that way you can address them by named Range instead of having to remember that Timer1 is range J5 and Timer 2 is J11

    It's bed-time here so I'm off to bed.
    Attached Files Attached Files

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Okay, the variable countdown, what would be the choices?

  7. #7
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Thank you! for putting the effort. The idea seems to be perfect, except that I can't see to get it to work. As soon as I open the spreadsheet, I get an error "Run time error '1004': Application defined or Object-defined error. Doing Debug highlights the following line:
    Please Login or Register  to view this content.
    Clicking on START gets a timer going but clicking on PAUSE or even RESET ALL TIMERS brings up the same error.

    Okay, the variable countdown, what would be the choices?
    I was looking at any starting number from 1 Minute to 20 minutes. I figured out a way to reference that in the code and also the conditional formatting from a single cell in the sheet. The value in the cell becomes the starting time. Either that or I guess it could be a drop down pick for 1, 2, 5, 10, 15, 20, minutes to make it easier for the user.

    On a side note, how can I lock the sheet so that a user can't modify any cell or object except for the variable value ? I tried locking the sheet with a password but then none of the Macros work! Locking the file as read-only also doesn't prevent a user from accidentally using right-click on a button and messing up the bottom. I am sure there is simple solution for this.

    Thanks again.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Well I did some searching but could not figure out why you get the error, I didn't, did you protect the worksheet?
    In that case that was the reason.
    I added some code and modified, the dropdown choice for each court is 1,2,5,10,15 or 20
    The worksheet is protected (no password) except for the counter value choices. there is an extra parameter you add when using vba and that is USerInterfaceOnly:=True this allows the VBA to modify any cell but not the user, only the unlocked cells are for the users
    I removes the Freezepanes and replaced it with ScrollLock (looks better )
    The conditional format for the DataBar is still on the 20 but I did not get around to that.
    Give it a run and let me know.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Strange. I am still getting the error as soon as the worksheet is opened! I did not protect any Worksheet and your Macro Sheet is the only one open. The choices on the error are Help, Continue, End or Debug. If I click on End, then I can click on the START Button which changes to PAUSE and the timer starts. However, if I click on PAUSE, the same error pops up. I wonder if its something specific to Mac Excel.
    Now I tried the Debug button and the code highlighted when the spreadsheet is first opened is
    Please Login or Register  to view this content.
    If I click on End, and continue the Macro's and click on PAUSE, the Debug highlighted code is
    Please Login or Register  to view this content.
    If I click on RESET, the Debug highlighted code is
    Please Login or Register  to view this content.
    Strange indeed!
    Attached Images Attached Images
    Last edited by Vinospam; 03-03-2019 at 10:58 AM.

  10. #10
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Reading a suggestion somewhere on the net, I moved the code from Sheet1 and Workbook to two new Modules and the Error on Open went away. However, the error on other button clicks still remain. Attaching the file.
    Attached Files Attached Files

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Very strange indeed, I made some minor modifications:
    Could you try again please.
    If still errors then I suggest you run one macro step by step:

    Opev the VBA editor and select in Module00 Reset_All_Timers
    press f8 or the equivalent on a MAC to step through the code, line by line.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    The only way I could even open the program without an error was to move the code from Sheet and Workbook to new Modules. Having done that, there is something strange with the START Button - it starts as PAUSE (see screenshot). Running the code from the VBA editor, the first error is indicated in Module01 at
    Please Login or Register  to view this content.

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Instead of removing the module just deactivate it.
    Still it's strange why this goes wrong.
    Here's my original file again with the Reset_All-Timers disabled.
    See if you can run that macro step by step and also the ResetTime1_Click
    One way or another MAC does not know what to do with character text boxes

    Later tonight I'll try something else.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Ok.
    • No Error on open sheet.
    • AssignMacros - No Error
    • Protectworksheet - No Error
    • ResetALLTimers - ERROR in Module 01 at
      Please Login or Register  to view this content.
    • ResetTimer1_Click - ERROR in Module 01 at
      Please Login or Register  to view this content.
    • All other ResetTimeXX Macros - ERROR in ModuleXX at
      Please Login or Register  to view this content.
    • StartALLTimers - No Error
    • StartTimer1Click - No Error
    • All other StartTimerXXClick - No Error
    • StopTimer1Click - ERROR in Module01 at
      Please Login or Register  to view this content.
    • All other StopTImer - ERROR in ModuleXX at
      Please Login or Register  to view this content.
    • Timer1Start - No Error
    • TimerXXStart - No Error
    • Timer1Stop - No Error
    • TimerXXStop - No Error
    • UnProtectworksheet - No Error

    Hope this helps

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Thanks for the extensive feedback and analysis
    I think that indeed the issue is the test of the text in the TextFrame
    I will approach it in another way. that will be some extra work, will try but don't think i'll get it done tonight.
    Will get back to you, hopefully with a working version, and since I don't have a MAC to test it on you'll be the one .

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Okay, hope this does it better
    I added 10 more buttons and renamed 10 of them
    Now you have the following buttons (names)
    ResetTimerX
    StartTimerX
    PauseTimerX
    ResumeTimerX
    Where X goes from 1 to 10
    40 buttons.
    The Visibility of StartTimerX, PauseTimerX and ResumeTimerX depends on the current state.
    When StartTimerX is pressed it becomes invisible and PauseTimerX is visible, pressing PAUSE PauseTimerX is hidden and ReSumeTimerX becomes visible and vv
    Pressing Reset will hide PAe and Resum,e and show Start again

    The actual code has been reduced to a series of common routines and only parameters are passed to make maintenance easier

    It's bedtime here.
    Good-night.
    Read your findings tomorrow and hope for good news
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Thumbs up Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Works Perfectly ! Thanks. Your tenacity is amazing!
    Yes - it was a Mac only issue since I ran your original sheet in Windows Excel on my VitualBox PC and it ran fine.

    Now I am going to work on the next couple of things to this to make it a bit more user-friendly:
    1. Fix the conditional formatting for the progress bar and other cells.
    2. Make the Start Time a Pick from a list on the sheet so that the Club can create their own set of start times instead of fooling with the sheet.

    I will work on it and post it. Thanks and good night.

  18. #18
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Here's the formatted sheet along with a drop down pick list.
    Any suggestions to improve are welcome but thank you again for all the assistance so for.

    A problem I see is the very tiny font for the dropdown list in Windows - on my Mac the list is readable but on Windows the font is very small. See screenshot.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Vinospam; 03-03-2019 at 10:27 PM.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Yes, i nogiced, If you Google youíll see itísan issue
    Aboutaleb the data bar (confitional format ) i couldnít find a way to make it work for other values them the serial time value 0.31......

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Am looking at options to modify the DATA BAR on-the-fly when the minutes change.
    About this, a USerform could be added to select another value and check the court or courts to apply it to
    Is this an idea?
    BTW, what are the numbers you added next to CRICKET COURTS?
    Last edited by Keebellah; 03-04-2019 at 04:21 AM. Reason: Extra question

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    The Dat Bar is an issue (for me at least) but I made some other modifications that hopefully help
    I added a Userform to modify one or all initial timer values, if a timer is running then that one cannot be modified until it's stopped (reset)
    I think it explains itsself

    Good-night
    Attached Images Attached Images
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    This is impressive. Let me try it out.
    The numbers along the cricket court was list of times to be used and I think you picked it up.
    I think there is still a Mac only bug. See screenshot. Meanwhile I will try it on a Windows computer. (I did and it worked on Windows)
    The code highlighted by the debugger is
    Please Login or Register  to view this content.
    I fixed the progress data bar by referencing the cell and it seems to work. See attached sheet.
    Also another issues I see is that the pop up sometimes remains active after the worksheet is quit.
    Thanks again.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Vinospam; 03-05-2019 at 01:45 PM.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    What do you mean with "pop up sometimes remains active after the worksheet is quit"
    Does it mean closing the workbook without first closing the userform?
    Do you want to hide the list/ You can also change the number format to ;;; (see attached screen shots)

    Can you explain step by step when the overflow error occurs?
    Attached Images Attached Images

  24. #24
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I think its better if the list is not hidden and the number format is also Ok. We will try this for a few days and see how it works out.
    The overflow error pops up as soon as I click on the Set Individual Timer Values Tab and the code is in the CountdownTimerForm.

    And I could not reproduce the "pop up sometimes remains active after the worksheet is quit" error - it must have been something to do with the Mac vs Windows problem.
    Last edited by Vinospam; 03-05-2019 at 02:24 PM.

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I will check and see

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Could you check if the overflow error still occurs?
    I did not change the version nr.
    Great that the Data Bar works,
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Yes, Unfortunately, it still happens. Interestingly, I just commented out this line:
    Please Login or Register  to view this content.
    and the error goes away. What exactly does this line do ?
    I have attached the program as version 2.5B.
    Attached Images Attached Images
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,527

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Looked like a challenge !
    And it was !
    Set Time exposes a combobox select from the dropdown this closes and you can start timers.
    Only tested a short while on 2010.
    torachan.
    Attached Files Attached Files

  29. #29
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    This line sets the top position for the userform
    You could try and just remove the part
    Please Login or Register  to view this content.
    and see if it still happens, that part addresses the ribbon height which is probably different for a MAC

  30. #30
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Nice one

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Deleteing that part of the code only places the user form up higher, let it be as you had it

  32. #32
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I removed the line of code causing the problems and also moved the userform to the right so you see all the 10 courts.
    And a new version nr

    I also liked torachan's dropdown boxes method but did not do anything with it since I like the idea to either modify one timer or have the option to modify them all at the time.
    He did however miss the 1 and 2 minute settings in the list in column A
    Attached Files Attached Files

  33. #33
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Thanks Keebellah and Torachan,
    Torachan: I haven't tried your version yet and I will as soon as I get Keebellahs version stable.
    Keebellah: Your version has no errors BUT on closing the Main Excel Window by clicking CMD-W (on a Mac), There is another Runtime error 5, Invalid Procedure or Argument. Debug leads to the following line in Module00
    Please Login or Register  to view this content.
    Commenting out this line eliminates this error. See screenshots of error.
    Thank you both!

  34. #34
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Same one, I'll check all the courrences and remove them.
    Send you the file asap

  35. #35
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Here's the file, All I did was add Exit Function in the three Functions that triggers the minimize and maximize ribbon, could also have added the option to test it it's a MAC then ignore.
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    This version seems to have done it (fingers crossed)
    Keebellah - really appreciate your help and I will post any updates here. Also will try to post a picture of how its being used in the real gym.
    Collaboration access the oceans was never so good !

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Good hear, yes.please show me (us) how it's being used.
    You know where to find me

  38. #38
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    One last test, a new version.
    I was looking at Torachan's sample and was wondering how to get the combobox to work with large fonts but that was a no go.
    His sample was nice but what I want to give as advice is like this file with 10 courts, name the buttons and combo boxes according to the court numbers. He started with court 10 as number 1 and then on to the right so Court 2 was Cbox10 as well as reser... 10 and that, but he did give me some ideas.
    What I did is throw away the DataValidation and added a Right-Click context menu that only works on the 10 cells where you want the timer value (see screen shot)
    I enlarged the buttons and also added the Mac test so that code lines are ignored if used on a MAC, the only thing I don't know if the context menu code works on a MAC.
    If it doesn't then just throw away the file
    Here's the file and screenshot
    Attached Images Attached Images
    Attached Files Attached Files

  39. #39
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Works perfectly on Windows. Right Click doesn't work on MAC and a runtime error pops up as soon as I right-click. The right click is a good idea but let me think about the interface options over the next few days and I will give feedback on interface that works well for the person doing the court timing. One option I can think of could be to start all courts at once - but only after a reset has been done.

  40. #40
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Okay, I thought that much, but anyway, I modified (including theMAC test) so that right click works only when on Windows
    I already had the macro start all timers built in but no button, so added button to start all and also to pause all
    See screenshot and attached modified file too.

    It's fun to play with VBA
    Attached Images Attached Images
    Attached Files Attached Files

  41. #41
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Have you tried running the same code in separate instances of Excel, one for each court? Not sure if that would work, but might be worth a try...

  42. #42
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    @gjcase: different instances for each court? This is a classical Microsoft difference between MAC and Windows versions
    But... be my guest

  43. #43
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Hi Alex, one last version from you 'stalker'
    Added some counters and buttons to pause, resume etc.
    Not tested om MAC but ....
    Attached Files Attached Files

  44. #44
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I was struggling a little with the code to make the timer choice dynamic. I managed and now the right-click menu depends only on the list of timer choices and not macro dependent
    The list allows for 20 values, I added two to test 150 minutes and 180 minutes
    I think that now you have enough and you probably had enough of me

    The final version
    Attached Files Attached Files

  45. #45
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I think you can mark the post as solved

  46. #46
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Couldn't help it but the app needed a Help Information form, added a button with a ?
    Hope it's all MAC compatible
    Attached Files Attached Files

  47. #47
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    @keebellah Sorry for being out of the picture for the last few days. The court timer has been working great. They are using version V2.7 and I am attaching some pictures of the facility. The timer has changed the dynamic of the facility and the courts are very well organized. They use the timers along with a restaurant style paging systems. When a court time is up, the manager activates a pager kept on the court and the players come off.

    I love the new interface with the help button - BUT - there's always a but..
    1. I had to move some text around.
    2. Right-click does not work on Mac. (not a problem at all)
    3. The cell for court color is Green for Starting, Yellow for In-Progress and Red for stopped. It would be nice to have another color (or pulsing ?) for Paused so that those courts can be located. I could change the conditional formatting but it might be simpler to change the Text and/or Button color of RESUME to something else other than red so that a Paused court can be easily identified.
    4. On Windows, with a new computer, the timer can run slower or faster (usually faster) when all courts are active. I don't see that problem on my Mac. The single threaded process may be getting overloaded.

    Again, thank you and the others for the great community work here.
    I am attaching my latest version 3.0.2 here.
    Last edited by Vinospam; 03-21-2019 at 06:50 PM.

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Looks great

    And about the colors, I had already thought of that

    What do you mean by move the text around? is the help too big?
    Attached Files Attached Files

  49. #49
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    On my Mac, the words ďCricket CourtsĒ were interfering with the Buttons. I just removed it. It might have been some text style difference between Mac/Windows.

    However, it looks like anyone can edit the All Court Start/Stop/Pause Buttons with a right click at any time even if the sheet itself is locked. Arenít they all supposed to be locked out ?

  50. #50
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    You shoild check the macro protectsheet, probably a remnant of the first version where caption was changed instead of showing or hiding the button.
    You could try by restricting more options to shed what happens , I left quite some few true because of Mac compatibility
    Iíll check later today

  51. #51
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Check this one please, Just court 1 start and pause then see if this resume is okay.
    Don't know if it works with MAC
    Attached Files Attached Files

  52. #52
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    There are many things to keep in mind when using a MAC:
    https://www.rondebruin.nl/win/s2/win014.htm

    Add button and menu to the Cell menu with RibbonX (Only Excel 2010-2016)
    If you want to insert RibbonX into a Excel 2010-2016 workbook to change the context menus, then I suggest you download and install the free Custom UI Editor on a Windows machine to make this a lot easier : http://openxmldeveloper.org/blog/b/o...muieditor.aspx

    Note: I suggest that Mac developers develop on a Windows machine so they can use the Custom UI Editor. Be aware that imageMso is not working in Mac Office 2016.

    Important : Mac Excel 2016 must be version 15.17 or higher, before that RibbonX is not working in Mac Excel 2016.

    What version of MAC Excel are you guys using?

    I've changed all the buttons to allow colouring
    I wonder if it all works on a MAC
    Attached Files Attached Files

  53. #53
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    This works very well. I will need to play around with the colors so that it looks good on the LED TV.
    I am using Mac Excel V16.23 as part of the Office 365 subscription.

    One suggestion from a manager is to identify the state of each court with text in addition to colors since some people cannot distinguish colors that well (to follow disability requirements).
    1. Status: At beginning or when Reset is clicked: Button says START: No additional Text needed.
    2. Status: when start is clicked: Button says PAUSE: Text on the court says: RUNNING
    3. Status: when Pause is clicked: Button says RESUME: Text on the court says: PAUSED

    Also, the Reset ALL tab & button and Start, Stop, Pause buttons in the cricket courts have an Are you sure? before the action since someone can accidentally click them and mess up all the courts.

  54. #54
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Okay, I managed some modifications:
    Added text under the Court saying Running, Paused and Resume depending on the status of each court.
    For the Reset All, Start All, Pause All and Resume All buttons there is a confirmation message box with default focus on the NO button.
    It is not advisable to do that for each individual court since you would need much more code to avoid questions when Reset ALL or Start ALL is pressed, it's a question of being careful.
    I added a little fun thing, so if you have speakers connected and a Court timer ends it calls out the court number and says the timer has ended, it's just the built in Speech function in Excel
    It's triggered only when the timer bar reaches 0, then it invokes the macro show_Ended

    In the vba module Admin0001 there is a Function named setAllButtonsTextAndColor
    There you can modify the color for the buttons as well as the font color and size

    Just modify and run the macro and it will reset all the buttons on the worksheet
    The RGB line is for the button's color
    The Colorindex is for the font.
    Each section is for different series of buttons, there are 4 groups and a loop from 1 to 10
    Attached Files Attached Files

  55. #55
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I forgot to mention, the initial zoom is set in the macro Begin_here
    I modified it to
    Please Login or Register  to view this content.
    And resized the HELP to the bottom right
    Attached Files Attached Files

  56. #56
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Sorry to stalk you again, but I think you might like this. Updated the last 3.0.4 again (today march 24th)
    Flashing ENDED when timer is completed as well as saying it (sound must be on and speakers or headphones connected)
    I tried to attach a short screenshot movie with the blinking showing but it's too large so you'll have to test it yourself and I hope the MAC takes it too
    Attached Files Attached Files
    Last edited by Keebellah; 03-24-2019 at 04:38 AM. Reason: Modified attachments

  57. #57
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    You're probably getting tired of me but ...
    Well, I did not like the message box it was to small and you cannot really change the format so I replaced with with a Userform
    Version number upped for administrative purposes
    Attached Images Attached Images
    Attached Files Attached Files

  58. #58
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Maybe thsi link works (mp4 file) https://1drv.ms/v/s!AuLzCGUYd6BTiZUQC1M6kc4I1N0O1A

  59. #59
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    This gets better and better. The folks at the courts are loving it.
    Couple of issues:
    1. The Speech function doesn't work on the Mac and throws up an error. See screenshot
    2. The NO Text color on the button inn the Userform is too light - I guess because its selected by default. Now that may be because of my Mac color palette.
    The court uses Windows so both of these are not really a problem.

  60. #60
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    You can modify this Macro; add the lines in red so that it works with Windows and skips if it's a MAC

    Please Login or Register  to view this content.
    The color of the BUTTON, I set the backcolor to RED and the text to WHITE
    Check if you the MAC palette or just make it deafault back color and black font

  61. #61
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Just in cae modified like this
    Attached Files Attached Files
    Last edited by Keebellah; 03-24-2019 at 09:09 AM. Reason: forgot attachment

  62. #62
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    The code worked. Perfect.
    Thanks!

  63. #63
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Good to hear, if you need more explanation about some of the macros, just holler, it's not complicated but maybe my track of mind might seem complicated.
    I like to reduce the number of procedures by allowing them to be re used for different routines

  64. #64
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Found some bugs while testing.
    If one or more timers have ended and you select start all timers it throws an error.
    Corrected it.
    I also noticed the speed up of one or two timers, not all the time but once in a while, I think this is due to the fact that several are triggered at almost the same time and it waits so sometimes this same trigger is run two times.
    Attached Files Attached Files

  65. #65
    Registered User
    Join Date
    03-02-2019
    Location
    Chicago, USA
    MS-Off Ver
    Excel for Mac 16.22
    Posts
    21

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Yes, the speed up of timers is a bit unnerving for some people. Can we try to make sure nothing is triggered at the same time by having a delay if two are close to each other.
    Played would rather have a few seconds more than few seconds less !

    BTW, this is version 4.0.2 in my collection.

  66. #66
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I will have to try something else, the case is that as many timers as playing courts are running at the same time.
    I might have an idea that I can try, just one timer that processes all 10
    Today won't be the day for it, it's my wife's Birthday and am not going to spend much time at my computer.

  67. #67
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    Did a quick one, think I got it all.
    Version now 4.1.0 ( first one for April (4))
    Hope the mac takes it too.
    One timer for all courts, will explain some time later. See the macro named update_ALL_Running
    Attached Files Attached Files

  68. #68
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    6,330

    Re: Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!

    I wrote a 4-page docuement as a user's guide for the application.
    While writing I did notice that the timer started 'skipping' when you were working with one court timer, paused it an restarted it.
    The reason is that if you a set various timers to run at 1 second intervals they all 'collide' causing the issue.
    This now seems to be solved. I saved the file as version 4.1.1.
    I'm attaching the file and the pdf as well. I also checked and a court's initial value can only be modified if the timer is stopped and, when entering the value manually only the values in the list on the right of the worksheet are accepted.
    This was a fun-project to have worked on, hope you guys may enjoy it for a long time, If I manage to get the right-click option working for a MAC I'll post it and if you do solve it, please share it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] timer and countdown
    By k1dr0ck in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-20-2018, 01:12 AM
  2. timer for 30 minutes and timer based events
    By mohak12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2014, 06:20 PM
  3. timer to countdown
    By Burt_100 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-10-2013, 07:27 AM
  4. Replies: 1
    Last Post: 12-12-2012, 08:46 PM
  5. Countdown timer Code not working
    By moses4u in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-02-2010, 12:42 PM
  6. Replies: 1
    Last Post: 03-10-2006, 08:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

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