Automatic saving and closing
 

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

Automatic saving and closing
 

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

Automatic saving and closing
 

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


"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

Automatic saving and closing
 

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

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.
--
tia

Jock


"Susan" wrote:

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

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" wrote in message
...
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.
--
tia

Jock


"Susan" wrote:

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

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.
--
tia

Jock


"Bernie Deitrick" wrote:

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" wrote in message
...
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.
--
tia

Jock


"Susan" wrote:

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

Jock,

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

HTH,
Bernie
MS Excel MVP


"Jock" wrote in message
...
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.
--
tia

Jock


"Bernie Deitrick" wrote:

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" wrote in message
...
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.
--
tia

Jock


"Susan" wrote:

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

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

Automatic saving and closing
 

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

"Bernie Deitrick" wrote:

Jock,

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

HTH,
Bernie
MS Excel MVP


"Jock" wrote in message
...
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.
--
tia

Jock


"Bernie Deitrick" wrote:

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" wrote in message
...
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.
--
tia

Jock


"Susan" wrote:

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


"Jock" wrote in message
...
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


"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

Automatic saving and closing
 

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