Prevent BeforeClose Event from triggering if workbook closes with shutdown timer

[Jerbinator]

Hi all,

I built a workbook that needs to be available to people throughout my office. Obviously, if someone opens it and just leaves it open all day, it becomes an issue for the other employees. In order to avoid this, I put in a shutdown timer macro that will save and close the workbook after 20 minutes.

I have a rather complex BeforeClose Event set up already. It asks if the user really wants to exit the workbook, then hides every sheet in the workbook except one, clears data from several tables, hides a bunch of form control buttons on the various worksheets that only the SuperAdmin is allowed to use (which only show up if you log in as a SuperAdmin upon opening the workbook), saves the workbook and then closes it.

Here's the problem: if someone times out in the workbook, I don't want that confirmation box to pop up, because then it will just keep sitting there and waiting for someone to do something to it and the workbook won't close, which defeats the whole point of a shutdown timer in the first place!

Is there a way to NOT fire the message box portion of the code if the user times out in the workbook? Everything else still needs to be done, but I don't want the message box to appear.

Here's the code for setting up the shutdown timer (it's in a module):

Please Login or Register  to view this content.

Here is the code for the BeforeClose procedure:

Please Login or Register  to view this content.

Any advice would be greatly appreciated. Thanks!

[Kaper]

How about:

Please Login or Register  to view this content.

[Jerbinator]

That works beautifully. Thank you!

[Jerbinator]

I wanted to add an update to this. It did work just fine, but I had to create a bit of a workaround. For some reason, the code was not recognizing the DownTime value, and so it was always acting as if the DownTime value had not been exceeded. So I changed the code for the timer so that when it established the DownTime, it would write that time to a cell on one of the worksheets. Then I had the BeforeClose code read that value instead. Worked like a charm after that!