I use the following code to automatically close the workbook after a period of inactivity. It's very much based on the work of others, but I think an improvement, because it works flawlessly (with caveat below) and is simple to implement.
The problem is if someone else was IN the workbook when the user opened it, the code still tries to save it when they exit, causing all sorts of problems because it can't, so it just plonks a copy of the workbook somewhere in the network share. And it shows an ugly error to the user.
Dim DownTime As Date Sub SetTime() DownTime = Now + TimeValue("00:7:00") Application.OnTime DownTime, "ShutDown" End Sub Sub ShutDown() Sheet37.Activate ThisWorkbook.Save ThisWorkbook.Close End Sub Sub Disable() On Error Resume Next Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _ Schedule:=False End Sub
Private Sub Workbook_Open() Call Disable Call SetTime Dim strFullFilename As String Dim OPENORNOT As Boolean strFullFilename = "O:\boombox\Information.xlsm" If (GetAttr(strFullFilename) And vbReadOnly) = 1 Then OPENORNOT = True MsgBox "Another user has info open." Else 'MsgBox "You CAN save this file!" End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheet37.Activate ThisWorkbook.Save 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
I tried to add IF statements using the boolean variable OPENORNOT to only "Thisworkbook.save" if the answer was FALSE, but that didn't work at all. Any tips? Anyone care to try this out a bit?