How to create timed excel message pop up on top of all apps to notify user that they’ve reached their use time limit in that excel file

Question

There are about twenty team members who take turns opening and editing an excel file. problem is that many times users forget the file open for extended periods which stops others from accessing to make their updates. Would like to have an “ok only” message box pop up that appears above all open apps after 5 minutes of inactivity in said excel file. Also would like it to freeze other apps from being used until the pop up is cleared by clicking “OK”. Also, after clicking “OK” I would like the same message box to reappear if there is another 5 minutes of inactivity. Also would like this message to only appear if the file is open as owner. The pop up should not appear if open read only. The hope is that this pop up reminder free’s up the file for updates without delay.

I already created this pop up and it works but I can’t get it to show above all open apps. Even if I have excel file showing on screen, if I have say email app as focus the pop up doesn’t display above all apps. The excel in task bar just flashes and only when I select it does the pop up show. I read that standard message box can’t be made to show on top of all apps and that a form has to be used but I’m new to forms also so if a form is the solution please help with form also. The code i'm currently using is as follows: Original Code

This is the error i get when using Tony's code.

Answer 1

I did something like this years ago, but it was very simple and since you seems to have problems with showing the message and will post code that I tested on Excel 2013

Paste this in ThisWorkbook:

The timer starts in the Workbook_Open event, this code test for 3 seconds and shows a MsgBox

Private Sub Workbook_Open()
    If Not ThisWorkbook.ReadOnly Then
        ' Check for owner here
        Application.OnTime Now + TimeSerial(0, 0, 3), "module.FileTimeout"
    End If
End Sub

Paste this in a module:

The Timeout function activates the Excel application with AppActivate and shows a MsgBox in vbModal, so it will show on the top of all oppened apps (in theory)

Public Sub FileTimeout()
    AppActivate "Microsoft excel"
    MsgBox "Hey! Time has passed, close this file so others can open it!", vbInformation + vbOKOnly + vbSystemModal, "Excel timeout"
End Sub

Like I said I tested this code in Excel 2013 and it worked, if I minimize all the windows the MsgBox it's showed in the top, if I do something else in other app, it shows the MsgBox in the top too

If the MsgBox does not shows in the foreground for whatever reason or you just want to show a custom message with UserForm you can check this answer to bring the form to the top