Hi There, I wonder if anyone can help me. I have a spreadsheet that auto closed (without saving) after 15 mins of inactivity. This works fine, but what I would like to do is display the idle time in a cell (say "A1") that starts to count down after 5 mins (thus counting down 10 mins to zero). If any interaction with the spreadsheet then the idle time will reset to 15 mins again. Is this possible or will the cell timer be considered as activity.
I can post snippets of code if required.
Thanks in advance.
Programming beginner but getting there :-)
'In Thisworkbook
Option Explicit
Private Sub Workbook_Open()
CreateObject("Wscript.Shell").Popup "This workbook will auto-close after 15 minutes of inactivity. Please save your work or it WILL be lost.", 5, "Auto-Close", 0
Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Call StopTimer
Call SetTimer
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
MsgBox "The 'Save' function for this Rota has been disabled. Please use 'Save As' or the 'Save' button on 'Rota Week 1' tab", vbOKOnly + vbInformation, "Save Disabled"
Cancel = True
End If
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call StopTimer
Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
' & In a module
Dim DownTime As Date
Sub SetTimer()
DownTime = Now + TimeValue("00:15:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=False
End Sub
Sub ShutDown()
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
End Sub
Bookmarks