Automatic saving and closing

[Jock]

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?

 

[Bernie Deitrick]

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]

Bernie,
Copied & pasted your code.
However, I get an error message:
Run-time error '1004'
Method 'on time' of object '_Application' failed

What have I done wrong here?

--
tia

Jock

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

 

[Susan]

maybe because you're forcing it to run & 20 minutes hasn't elapsed yet?
or maybe not - just a thought.

susan

Bernie,
Copied & pasted your code.
However, I get an error message:
Run-time error '1004'
Method 'on time' of object '_Application' failed

What have I done wrong here?

 

[Jock]

If only it was that easy.
All I did (after entering the code) was change the value in a cell and
tabbed out of it. The error message appears immediately.

 

[Bernie Deitrick]

Jock,

You got that message because there was no ontime event to cancel. I assumed that you would first
save the workbook and your users would re-open it prior to making any changes.

Either run this macro to initialize the ontime method:

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

or add

On Error Resume Next

to the sheet change event:

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

On Error Resume Next
Application.OnTime RunTime, "SaveAndCloseMe", , False
RunTime = Now() + TimeValue("00:20:00")
Application.OnTime RunTime, "SaveAndCloseMe"
End Sub


Sorry about that,
Bernie
MS Excel MVP

 

[Jock]

Thanks Bernie, works a treat.
I have noticed however, if the user enters data but doesn't tab out or press
<enter> to move to another cell, the workbook doesn't close.

 

[Bernie Deitrick]

Jock,

VBA code is disabled when Excel is in Edit mode, so there is no recourse except user training.

HTH,
Bernie
MS Excel MVP

 

[br549]

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?

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

 

[csr9632]

And then, how can i check the edit cell mode by using excel.application object?

 

[Glen]

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

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?