Option in Excel to save a close a workbook inactive for 5 minutes
 

We share workbooks and many times users forget to close and go to meetings,
home...etc. Has anyone found a work around solution to this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

You could use Ontime together with worksheet change event code to monitor
it.

There are 4 elements here.

Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook and
reset the timer

Option Explicit

Public nSaveWB As ndate

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
ActiveWorkbook.Save
SetSaveWBTimer
End Sub

You then need to set the timer in the first place, when the workbook opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).

Option Explicit

Private Sub Workbook_Open()
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patricia Peterson" <Patricia wrote in
message ...
We share workbooks and many times users forget to close and go to
meetings,
home...etc. Has anyone found a work around solution to this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...lic.excel.misc

Just noticed you said close, so change

Public Sub SaveWB()
ActiveWorkbook.Save
SetSaveWBTimer
End Sub


to

Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You could use Ontime together with worksheet change event code to monitor
it.

There are 4 elements here.

Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook and
reset the timer

Option Explicit

Public nSaveWB As ndate

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
ActiveWorkbook.Save
SetSaveWBTimer
End Sub

You then need to set the timer in the first place, when the workbook
opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).

Option Explicit

Private Sub Workbook_Open()
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patricia Peterson" <Patricia wrote in
message ...
We share workbooks and many times users forget to close and go to
meetings,
home...etc. Has anyone found a work around solution to this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...lic.excel.misc

and a typo

Public nSaveWB As ndate

should be

Public nSaveWB As Date

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
Just noticed you said close, so change

Public Sub SaveWB()
ActiveWorkbook.Save
SetSaveWBTimer
End Sub


to

Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
You could use Ontime together with worksheet change event code to
monitor
it.

There are 4 elements here.

Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook
and
reset the timer

Option Explicit

Public nSaveWB As ndate

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
ActiveWorkbook.Save
SetSaveWBTimer
End Sub

You then need to set the timer in the first place, when the workbook
opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).

Option Explicit

Private Sub Workbook_Open()
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Patricia Peterson" <Patricia wrote
in
message ...
We share workbooks and many times users forget to close and go to
meetings,
home...etc. Has anyone found a work around solution to this?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.




http://www.microsoft.com/office/comm...lic.excel.misc

Option in Excel to save a close a workbook inactive for 5 minutes
 

Hi there,

I tried the steps above with all the typo changes but could someone
please tell me how to set this up in the macro - i used the code below
in 'this workbook' and waited but nothing happened.

Help please (i'm sure i'm just having a thickie spell)

Option Explicit

Public nSaveWB As Date

Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 1, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub

Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
Option Explicit

Private Sub Workbook_Open()
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=390704