Timer Broken

[WaveWalker116]

Hi all,

I have a timer on my spreadsheet so that when others have it open and leave for the night or go to a meeting, it starts timing 4 minutes from the last entry and then counts down 1 minute in 1 second intervals until it saves the spreadsheet and closes it.

What's happening is, when you first open the document everything works, it seems to count the full 4 minutes and then starts the timer.

However, once you hit cancel, it seems to randomize how long until it rethrows the timer. So, I open it the first time, wait the 4 minutes until the timer comes up, hit cancel, then wait again... one time it threw it in 32 seconds. I hit cancel, it threw it in 2 minutes, hit cancel, it threw it in 1 minute.

Also, what's weird is, if you enter data before it throws the timer the first time, it seems to reset the count and counts another 4 minutes. But once it throws the timer, it doesn't seem to care if you're entering data, it just keeps throwing the timer at you at random times.

Again, the goal is, every 4:00 minutes of inactivity, it throws the window and starts counting down until it saves and closes. If you hit the cancel button or are entering data, it would reset the timer and restart counting down from 4 minutes.

I have attached a sample spreadsheet, but here's my code for the timer itself:

Please Login or Register  to view this content.

And here's my code for Module 2:

Please Login or Register  to view this content.

Does the...

Please Login or Register  to view this content.

... mean I have to SAVE it every 4 minutes to stop the timer from coming up?

Not sure what I missed, but it definitely seems sporadic in what it does. Any help would be greatly appreciated!!!

[GWteB]

Hi, starting with your final question:

Does the...

Please Login or Register  to view this content.

... mean I have to SAVE it every 4 minutes to stop the timer from coming up?

SaveWorkbook is a tailor made property of the (class) module of your userform. This code snippet checks whether that property is True or False, so code execution branches dependent on the actual outcome.

The timer is not broken, your code just doesn't perform as intended. Summarizing your current code: any change to any worksheet will cancel a previous scheduled macro (if any) and will set a new scheduled time. The scheduled macro puts a modeless Userform on screen. Note that a modeless Userform enables users to make changes on worksheets while that Userform is still on screen. Because any change to any worksheet ... (etc.). This is most likely the cause of your issue, worksheet changes made with your dialog displayed on screen. Let me explain.

The visual countdown is performed within a loop. The scheduled macro which is responsible for putting the Userform on screen and executing the mentioned countdown loop does not continue until that loop ends (either on pressing cancel or when 0 is reached). When that macro continues a new time will be scheduled, however currently without cancelling any previous scheduled macro. This results in the macro executing multiple times, thus giving the impression that the interval is randomly shorter than intended.
The bottom line is that in the required scenario any previous schedule always needs to be canceled before setting a new schedule.

By using a modal userform users cannot make worksheet changes while the userform is displayed, so preventing issues as experienced.
If a modeless userform is required it would be preferable in my opinion that the countdown dialog automatically disappears from the screen when a worksheet is changed. The code below takes this into account.

This goes in a standard module:

Please Login or Register  to view this content.

This goes in the userform's module:

Please Login or Register  to view this content.

Hope this helps.

[WaveWalker116]

Awesome... and thanks so much for the explanation and notes. I learned a TON. Very much appreciative of your time and it looks like it's working flawlessly now.

Thank you!!