Something along these lines.
In a standard module:
Sub StartTimer()
Timeout = Now() + TimeValue("00:00:10")
On Error Resume Next
Application.OnTime EarliestTime:=Timeout, _
Procedure:="FinalAction", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=Timeout, _
Procedure:="FinalAction", Schedule:=False
End Sub
Sub FinalAction()
If ThisWorkbook.ReadOnly = False then
ActiveWorkbook.Close Savechanges:=True
'or show a userform
End If
End Sub
Then in the Workbook module, start/stop the timer when the workbook's opened/closed.
Reset it at various events.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
Private Sub Workbook_Open()
Call StartTimer
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call StopTimer
Call StartTimer
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Call StopTimer
Call StartTimer
End Sub
The above saves and closes the workbook without a prompt if the workbook is not read only. For the popup, you can't use a normal message box as that will pause the code execution, so you'd probably want to make one with a userform that has a countdown so that it'll continue to close if no response is received from the user.
Bookmarks