Just to bring closure to this thread, as it took me a while to find the
answer to this question as well...

Add the following code to Thisworkbook object

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunTime, "SaveAndCloseMe", , False
End Sub

See Chip Pearson's page on OnTime for discussion on the finer points of
cancelling something queued using OnTime.

Cheers,

Glen



"br549" wrote:

I installed this code this week and have been very pleased (thank you
Bernie). One problem though: if I have multiple workbooks open, then close
the workbook that has the timer running and continue working in the other
workbooks, after the timer in the original workbook counts down the VBA
editor window pops up and gives an error message (sorry, I forgot to capture
it). Does something else need to be done to stop the timer when the timed
workbook is closed?

"Bernie Deitrick" wrote:

Jock,

Put this code into a regular codemodule:

Public RunTime As Date

Sub SaveAndCloseMe()
Application.DisplayAlerts = False
ThisWorkbook.Close True
Application.DisplayAlerts = True
End Sub


And put this code into the ThisWorkbook object's codemodule:

Private Sub Workbook_Open()
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub

It will save and close the workbook after it hasn't been changed for 20 minutes.

HTH,
Bernie
MS Excel MVP


"Jock" wrote in message
...
Quite often, somone in our office has a certain file open to which others
need access. The person who has it open hasn't made changes for, say 20 mins.
Is it possible to have the spreadsheet automatically save and close after a
specified time period has elapsed?
--
tia

Jock