Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
Posted to microsoft.public.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel save changes | Excel Discussion (Misc queries) | |||
Can't save Excel using Save button, | Excel Discussion (Misc queries) | |||
Save? prompt when closing Excel | Excel Discussion (Misc queries) | |||
Excel workbook fails to display when selecting from my documents. | Excel Worksheet Functions | |||
Excel workbook does not open in open window on desktop | Excel Discussion (Misc queries) |