How to automatically save and close an Excel file after a certain idle time?
In some cases, such as leaving an Excel file on a shared disk, when the file is still opening by a user, others are unable to edit the workbook. So how to auto close the file after a certain idle time in order to ease other’s work? This article will help you with VBA method.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To auto save and close an Excel file after a certain idle time, please do as follows.
1. Open the workbook you need to make it auto saved and closed after a certain idle time. Then press the Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, please double click the ThisWorkbook in the right pane to open the Code window. Then copy and paste below VBA code into the Code window. See screenshot:
VBA code 1: Auto save and close an Excel file after a certain idle time
Dim xTime As String Dim xWB As Workbook Private Sub Workbook_Open() 'Updated by Extendoffice 2019/1/20 On Error Resume Next xTime = Application.InputBox("Please specify the idle time:", "KuTool For Excel", "00:00:20", , , , , 2) Set xWB = ActiveWorkbook If xTime = "" Then Exit Sub Reset End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error Resume Next If xTime = "" Then Exit Sub Reset End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next If xTime = "" Then Exit Sub Reset End Sub Sub Reset() Static xCloseTime If xCloseTime <> 0 Then ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , False End If xCloseTime = Now + TimeValue(xTime) ActiveWorkbook.Application.OnTime xCloseTime, "SaveWork1", , True End Sub
3. Then go on clicking Insert > module, and copy and paste below code into the Module window. See screenshot:
VBA code 2: Auto save and close an Excel file after a certain idle time
Sub SaveWork1() 'Updated by Extendoffice 2019/1/20 Application.DisplayAlerts = False ActiveWorkbook.Save ActiveWorkbook.Close Application.DisplayAlerts = True End Sub
4. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
5. Click File > Save As > Browse. See screenshot:
6. In the Save As dialog box, please select a folder to save the file, name it as you need in the File name box, select Excel Macro-Enabled Workbook from the Save as type drop-down list, and finally click the Save button. See screenshot:
From now on, every time when opening this workbook, a Kutools for Excel dialog box will pop up. Please enter the time you will save and close the workbook based on, and then click the OK button.
And the workbook will be saved and closed automatically after that specified idle time. See screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoIs there a way to make this work so even if you not on the sheet like click over to another window the timmer will still close that sheet? It seems like i have to stay on the file for it to work
To post as a guest, your comment is unpublished.· 2 months agoI would recommend identifying the workbook with the workbook file name. This would allow the users to have multiple workbooks open and closing out only the specific workbook that needs to have the time lock.
Suggestion: Set xwb = Workbooks("<file name>")
instead of: Set xwb = ActiveWorkbook
Also, replace all references to "ActiveWorkbook" to the Xwb in the module
Great code otherwise.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 6 months agoHi Thanks loads for this code. Just one issue. It seems to try to close all open workbooks thus stalls if another workbook close is cancelled or not responded to. have you any touch-ups that can fix this?
To post as a guest, your comment is unpublished.· 3 months agoHi Henry,
The code has been updated with the problem solving. Please have a try. Sorry for the inconvenience and thank you for your comment.
To post as a guest, your comment is unpublished.· 5 months agoI have the same issue... I tried to open a new workbook, and entered some data.... when the time came, both workbooks were closed, but the new one wasn´t saved... Is there a way to fix this?
Besides that, it works great!! Thanks a lot!!