Prevent VBA Application.Ontime running if windows locked.

  • Hello, thank you for reading:

    I have a workbook with timed events using Application.Ontime referencing a global variable (GlobalTimer) that either opens a form (called TimeOut), or closes the workbook (to prevent users leaving a workbook open on a networked drive preventing others from using it). The form that opens is a warning that the sheet will close in x minutes unless a response is obtained, the form itself then closes after a few seconds.

    This all works well except if windows is locked in which case the Application.WindowState = xlMaximized and Userform.Show commands do not run until the computer is reopened and if minimised the icon in the command bar clicked on.

    Is there a way to detect if lines have failed/is failing to display the form and subsequently simply close the workbook?

    NB: If I simply want to close the workbook, without giving warning, this works fine even if the computer is locked. it is just the loading of the form while the computer is locked that is causing an issue.

    the code in the workbook is like this:

    The code in the module:

    The code linked to the form:

    Private Sub UserForm_Activate()
    Dim MyTime As Date
    MyTime = Now()
    Application.OnTime MyTime + TimeValue("00:00:05"), "ClsTIMEOUT"
    End Sub

    There are a couple of buttons on the form that either change the globaltimer or does nothing.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!