Background code to see how long the spreadsheet has been open

Question

I want to evaluate how long the spreadsheet has been open and once it hits a certain time (say an hour), warn the user that it will automatically close in 15 mins.

I have a tab called Reference where I can put auto-open code to log when they opened it but I want the spreadsheet to check unprompted at certain intervals how long the sheet has been open.

I want to force a close where a user has accidentally left a spreadsheet open so it's important it isn't assessed based on user input.
Note - I don't care if they lose work, I just want to close the spreadsheet.

Have a look at Application.OnTime: https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.OnTime Put it into Workbook.Open-Trigger — FunThomas, Jul 22 '20 at 14:49
Thank you so much! I had no idea this was a thing. It definitely sounds like that will work. — TerrifiedJelly, Jul 22 '20 at 14:52
FunThomas, if you post this as an answer, I can accept it. Currently all I can do is upvote it for you. — TerrifiedJelly, Jul 22 '20 at 15:06

FunThomas · Accepted Answer · 2021-12-06T08:17:24.793

To schedule the call of a routine, use Application.OnTime. To ensure that the routine is scheduled to "one hour after opening", create a Workbook-Open sub (in ThisWorkbook-Module) and put something like this into it:

Private Sub Workbook_Open()
    Application.OnTime DateAdd("h", 1, Now), "InformUser"
End Sub

This will call the Sub InformUser one hour after the Workbook was opened. In this routine you could add another call to OnTime. However, you need to be a little bit careful how to inform the user. If you simple use MsgBox but the user is not reacting to that, the MsgBox-Window stays active and as it is a modal window, the code will not continue to run and the OnTime-procedure will never be triggered. It's not easy to create a MsgBox with a TimeOut (see Display a message box with a timeout value), so maybe create a simple form and show it Non-Modal.

Sub InformUser()
    InfoForm.Show modal:=False
    Application.OnTime DateAdd("n", 15, Now), "ShutDown"   ' "n": Minutes ("m" stands for months)
End Sub

Sub ShutDown
    ThisWorkbook.Close SaveChanges:=True   ' Or "False" if you don't want to save - use on your own risk...
End Sub

Afaik `OnTime` 's procedure argument should be a string, here e.g. `Application.OnTime DateAdd("n", 15, Now), "InformUser"` — T.M., Dec 04 '21 at 10:07

Background code to see how long the spreadsheet has been open

1 Answers1