Sorted
When other workbooks are automatically closed the workbook that is the main menu is closed and reopened and "SetTime" called. This was the workaround for after the first "close workbooks", as the SetTime did not automatically restart, and clicking a macro button on the menu did not constitute a "SheetSelectionChange". If after the first close a workbook was opened from a macro button, there was no more timed "closed if inactive".
This way it continues in a loop until the "menu" workbook is closed.
In Workbook
Private Sub Workbook_Open()
Dim SH As IWshRuntimeLibrary.WshShell
Dim Res As Long
Set SH = New IWshRuntimeLibrary.WshShell
Res = SH.Popup(Text:="THESE WORKBOOKS ARE SET TO AUTOMATICALLY SAVE & CLOSE IF NOT IN USE FOR 30 MINUTES", secondstowait:=3, _
Title:="CONTENT SECURITY PROTECTION", Type:=vbOKOnly)
Call SetTime
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
End Sub
Private Sub Workbook_SheetCalculate(ByVal SH As Object)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal SH As Object, ByVal Target _
As Excel.Range)
Call Disable
Call SetTime
End Sub
In module
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:00:10")
Application.OnTime DownTime, "ShutDown"
End Sub
Sub ShutDown()
Dim SH As IWshRuntimeLibrary.WshShell
Dim Res As Long
Set SH = New IWshRuntimeLibrary.WshShell
Res = SH.Popup(Text:="THE OPEN WORKBOOKS HAVE NOT BEEN USED FOR 30 MINS AND WILL NOW SAVE & CLOSE AUTOMATICALLY IN 10 SECONDS - SCREEN WILL RETURN TO MAIN MENU", secondstowait:=2, _
Title:="CONTENT SECURITY PROTECTION", Type:=vbOKOnly)
Dim wb As Workbook
For Each wb In Workbooks
If Not wb.Name = ThisWorkbook.Name Then
wb.Close SaveChanges:=True
End If
Next wb
Call OpenMe
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
Schedule:=False
End Sub
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
Range("j6").Value = 1 + Range("j6").Value
Call SetTime
End Sub
A counter was added to cell j6 to make sure workbook was opening and closing and can be hidden to monitor how many times the macro was invoked in use
Ta Nigel
Bookmarks