Skip to main content

How to automatically save and close an Excel file after a certain idle time?

Author: Siluvia Last Modified: 2020-06-18

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.

Auto save and close an Excel file after a certain idle time with VBA code


Auto save and close an Excel file after a certain idle time with VBA code

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:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello good afternoon! I would like MSBox not to appear when opening the file. I don't want them to change the configured closing time.
This comment was minimized by the moderator on the site
The Problem is it`s a file with links and every time you start it, it will ask you if you would update it and read only mode?
This comment was minimized by the moderator on the site
Hello
Code works but if i close the file before the time runs out, It will open it again and close. how can i stop the code when i close the file manual.
This comment was minimized by the moderator on the site
Hi,

I tested the code and it did not reproduce the problem you mentioned. Can I ask which version of Excel you are using?
This comment was minimized by the moderator on the site
Hi
It is Microsoft Excel 365 MSO (Version 2308) 32 bit.
This comment was minimized by the moderator on the site
This is my Code:

Dim xTime As String
Dim xWB As Workbook

Private Sub Workbook_Open()
On Error Resume Next
xTime = "00:00:35"
Set xWB = Workbooks("Produktionsbericht - Kopie.xlsm")
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
xWB.Application.OnTime xCloseTime, "SaveWork1", , False
End If
xCloseTime = Now + TimeValue(xTime)
xWB.Application.OnTime xCloseTime, "SaveWork1", , True
End Sub
This comment was minimized by the moderator on the site
Hello
All works at my file but i have a new Problem.
If i close the file before the time runs out, it will open it again and close it when the time runs out.
Can i stop the VBA Code when i close the File myself?
This comment was minimized by the moderator on the site
Code is not working if the module is protected, is there any way to get the code run if module is protected.
This comment was minimized by the moderator on the site
Hi Chinna,

If the module is protected, the code is hidden and cannot be run. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Perdonen, si se pudiera modificar el código o adaptarlo para que reaccione con formularios (UserForm), porque solo reacciona al estar activo en las celdas pero no en los formularios, gracias
This comment was minimized by the moderator on the site
Hi Moises Peraza,
UserForm does not affect the use of the VBA code. Can you describe the problem you encountered more clearly.
This comment was minimized by the moderator on the site
Gracias por responder, el inconveniente que tengo es: le pongo de tiempo de inactividad 2 min. En el momento de trabajar con formularios (ingresar información del cliente), pasado ese tiempo me cierra o me tira el mensaje que se cerrara, espero darme a entender
This comment was minimized by the moderator on the site
Hi mister, how to view the countdown?
This comment was minimized by the moderator on the site
Hi mister, how to view the countdown?
This comment was minimized by the moderator on the site
(Sigh.)
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.
This comment was minimized by the moderator on the site
look below, Mark's comment : "I 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."
This comment was minimized by the moderator on the site
Hi. I need to set my shared spreadsheet on the office network to auto save and close when I'm done with it for the day. Only my boss and I are supposed to make and save changes, but several managers view it daily.
I need to be able to have it auto save for the two of us, while not saving for other users. Is this possible? Can I set the macro to work only for us? Or will it effect everyone?
I know how to protect the sheet, but I can't do that, because they want to be able to make some temporary changes from time to time. I just need to be able restrict auto save to specific users.
This comment was minimized by the moderator on the site
Is there a way to change this so it prompts the user to save/close the file, instead of automatically saving and closing the file?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations