PDA

View Full Version : Solved: Save and Close automatically if inactive



GoKats78
03-22-2010, 06:19 AM
I have several spreadsheets that are used by multiple persons across the plant. We have a problem with people walking away from their machines for hours leaving the speadsheets open. I need a bit of code to save and close the file if it is inactive for "x" amount of time.

Jan Karel Pieterse
03-22-2010, 07:19 AM
In the ThisWorkbook module, insert this:


Option Explicit
Dim mdNextTime As Double
Dim mbClosing As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
mbClosing = True
End Sub
Private Sub Workbook_Open()
mdNextTime = Now + TimeValue("00:10:00")
Application.OnTime mdNextTime, "CloseMe"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
'Cancel previous scheduled event
Application.OnTime mdNextTime, "CloseMe", , False
'Schedule new event
mdNextTime = Now + TimeValue("00:10:00")
Application.OnTime mdNextTime, "CloseMe"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
'Cancel previous scheduled event
Application.OnTime mdNextTime, "CloseMe", , False
'Schedule new event
mdNextTime = Now + TimeValue("00:10:00")
Application.OnTime mdNextTime, "CloseMe"
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
If mbClosing Then
On Error Resume Next
'Cancel scheduled event
Application.OnTime mdNextTime, "CloseMe", , False
End If
End Sub

Then in a normal module, add this:


Option Explicit
Sub CloseMe()
ThisWorkbook.Close True
End Sub

GoKats78
03-22-2010, 07:33 AM
That did it! Thanks...this place has been a great find!