How to save and close workbook after inactivity for a certain amount of time?
In some times, you may accidently close a workbook when you are busy with other affairs for a long time which may lose some important data in the workbook. Is there any tricks to automatically save and close the workbook if you have inactivated it for a certain amount of time?
There is no built-in function in Excel to solve this problem, but I can introduce a macro code which can help you to save and close workbook after inactivity in a certain time.
1. Enable the workbook you want to automatically save and close after inactivity for a certain seconds, and press Alt + F11 keys to open Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a Module script, and paste below code to it. See screenshot:
Dim CloseTime As Date Sub TimeSetting() CloseTime = Now + TimeValue("00:00:15") On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="SavedAndClose", Schedule:=True End Sub Sub TimeStop() On Error Resume Next Application.OnTime EarliestTime:=CloseTime, _ Procedure:="SavedAndClose", Schedule:=False End Sub Sub SavedAndClose() ActiveWorkbook.Close Savechanges:=True End Sub
3. Then in the Project Explorer pane, double click This Workbook, and paste below code to the beside script. See screenshot:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call TimeStop End Sub Private Sub Workbook_Open() Call TimeSetting End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Call TimeStop Call TimeSetting End Sub
4. Go to double click at the module you inserted in step 2, and press F5 key to run the code. See screenshot:
5. Then after 15 seconds, there is a dialog popping out for remind you saving the workbook, and click Yes to save and close the workbook.
(1) In the first code, you can change the inactivity time to other in this string: Now + TimeValue("00:00:15")
(2) If you have never saved the workbook before, the Save As dialog box will come out firstly and ask you to save it.
|Kutools for Excel's Protect Worksheet function can quickly protect multiple sheets or the whole workbook at once.|