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
DownTime = Now + TimeValue("00:7:00")
Application.OnTime DownTime, "ShutDown"
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
Private Sub Workbook_Open()
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."
'MsgBox "You CAN save this file!"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
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?