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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.