Auto-Close Excel file after 10 minutes

[scottchampion]

I would like to create a macro which will automatically close the
excel 10 minutes after it is opened. I've seen posts containing code
that will close a file, but I don't see any code that does it on a
timed basis.

Can you help me? Also, where would I put such a macro?

 

[Paul B]

I don't know where I got this code, but try this, as is will save and close
the workbook in 20 seconds, use to test, for 10 minutes change
TimeValue("00:00:20") to TimeValue("00:10:00")

'this will auto-close the workbook after 20 seconds of inactivity
'***************************************
'put this in a standard module
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:20")
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Close True
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub
'*******************************************

'************************************
'put this in thisworkbook
Private Sub Workbook_Open()
MsgBox "This workbook will auto-close after 20 seconds of inactivity"
Call SetTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
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
'*****************************


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[scottchampion]

Thank you. This code worked well. One quick word of warning to other
people out there, a few of the lines of code above are word-wrapped
and caused some compile errors.

On a side note, I changed the code of the private functions to disable
to "auto close" feature if the file is opened as read only.

*********************************************************************
'replacement code for workbook
Private Sub Workbook_Open()
If Not ThisWorkbook.ReadOnly Then
MsgBox "This workbook will auto-close after 20 seconds of
inactivity"
Call SetTime
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ThisWorkbook.ReadOnly Then
Call Disable
End If
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Not ThisWorkbook.ReadOnly Then
Call Disable
Call SetTime
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Excel.Range)
If Not ThisWorkbook.ReadOnly Then
Call Disable
Call SetTime
End If
End Sub
***************************************************************

Thanks again and godspeed!
Scott