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 SubThe 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 SubThe code linked to the form:
Private Sub UserForm_Activate()
Dim MyTime As Date
MyTime = Now()
Application.OnTime MyTime + TimeValue("00:00:05"), "ClsTIMEOUT"
End SubThere are a couple of buttons on the form that either change the globaltimer or does nothing.