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:
Private Sub Workbook_Open() ThisBook = Application.ActiveWorkbook.Name 'set the variable to the current time GlobalTimer = Now() 'Opening workbook so check in 40sec minutes Application.OnTime Now() + TimeValue("00:00:40"), "CheckTimer1" Application.OnTime Now() + TimeValue("00:02:00"), "CheckTimer2" FRONT.Activate Call HideShts FRONT.ScrollArea = "A1" FRONT.Protect 'SHOWDIRECTORY End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) GlobalTimer = Now() End Sub
The code in the module:
Option Explicit Public GlobalTimer As Date Public ThisBook As String Public Function ClsTIMEOUT() Unload TIMEOUT End Function Public Function CheckTimer1() As Boolean Dim x As Long 'check to see if we have a 5 minute interval TIMEOUT.Show If DateDiff("n", GlobalTimer, Now()) >= 0.5 Then Call SavenClose Else 'check again in x amount of minutes - currently it is .5 minutes Application.OnTime Now() + TimeValue("00:00:30"), "CheckTimer1" End If End Function Public Function CheckTimer2() As Boolean Dim x As Long 'check to see if we have a 5 minute interval 'Application.WindowState = xlMaximized 'TIMEOUT.Show If DateDiff("n", GlobalTimer, Now()) >= 0.5 Then Call SavenClose Else 'check again in x amount of minutes - currently it is .5 minutes Application.OnTime Now() + TimeValue("00:00:30"), "CheckTimer2" End If End Function Sub SavenClose() Dim frm As UserForm For Each frm In UserForms Unload frm Next frm On Error Resume Next Application.DisplayAlerts = False Workbooks(ThisBook).Saved = True Workbooks(ThisBook).Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
The code linked to the form:
There are a couple of buttons on the form that either change the globaltimer or does nothing.