Can anyone help me:

I have built a spreadsheet that my team of 5 use regularly. If someone
has it open then others can't use it. sometimes ppl forget to close
it.

Is there any way I can tell MS Excel to save the worksheet and close
it after 5 or 10mins idle?

Thanks,
MB

Hi Mack,

I have built a spreadsheet that my team of 5 use regularly. If someone
has it open then others can't use it. sometimes ppl forget to close
it.

Is there any way I can tell MS Excel to save the worksheet and close
it after 5 or 10mins idle?


You could consider sharing the workbook, but I discourage sharing Excel
workbooks because it is unreliable.

To your question:

There is no "activity timer" built into Excel, but we can use the ontime
method to schedule a closure of the workbook and reschedule the set time
at all of Excel's built in workbook events.

Open the VBE and insert a normal module, paste in this code:

'-------------------

Option Explicit

Public dNextTime As Double

Sub SetNewTimer()
ResetTimer
'adjust the time below to your needs, this is 10 minutes
dNextTime = Now + TimeValue("00:10:00")
Application.OnTime dNextTime, "CloseMe"
End Sub
Sub ResetTimer()
Application.OnTime dNextTime, "CloseMe", False
End Sub
Sub CloseMe()
ThisWorkbook.Close SaveChanges:=True
End Sub

'-------------------

Now open the Thisworkbook module and paste this code:

'-------------------

Option Explicit

Private Sub Workbook_Activate()
SetNewTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ResetTimer
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
SetNewTimer
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
SetNewTimer
End Sub

Private Sub Workbook_Deactivate()
SetNewTimer
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
SetNewTimer
End Sub

Private Sub Workbook_Open()
SetNewTimer
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
SetNewTimer
End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
SetNewTimer
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
SetNewTimer
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
SetNewTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
SetNewTimer
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
SetNewTimer
End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal
Target As Hyperlink)
SetNewTimer
End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal
Target As PivotTable)
SetNewTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
SetNewTimer
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
SetNewTimer
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
SetNewTimer
End Sub

Private Sub Workbook_WindowResize(ByVal Wn As Window)
SetNewTimer
End Sub

'-------------------


Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com