Have a look at the code.
If you put this VBA into Excel001.xlsm, but, when the timeout occurs, you have Excel002.xlsm as the "active" workbook, then it's actually going to close THAT Excel file, rather than the one you've put the VBA into.
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.
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: