Excel Tip: Closing Excel File After Idle Time - Step by Step
Learn how to auto save and close excel file that has been idle for specific period of time.
Posted: Jun 2010
In case that you have Excel file on shared disk, you know that only one person at time can have Write authorization. All other users that access that file, while first user is still using the file, can have only Read authorization. This is normal situation.
But probably you faced situation, for many times, that some user opened the file and forget to close it. If there are multiple potential users, than this could be a real problem.
One solution is that you call every user until you find who left the file open and ask that particular user to close file. This is really annoying, especially if there are many users and if they the leave office.
The other solution is to use Microsoft Excel Macro that will auto save and close the excel file, after specific period of idle time. If you do not know how to use excel macro or Microsoft visual basic editor just follow step by step instruction:
Open the excel file and go to View/Macos, select This Workbook, type the name in the Macro name box ( no space between words ) and click Create. The Microsoft Visual Basic will open.
Click to ThisWorkbook in the Visual Basic editor and paste the following code:
Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Click to Module1 and paste the following code. You can edit the time period after which the file will auto save and close. Just edit "00:00:20" which is default ( HH:MM:SS).
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
SchedSave = Now + TimeValue("00:00:20") ' 20 seconds
Application.OnTime SchedSave, "SaveWork", , True
Save the file.
That's it. Do not forget to enable macros on all computers, otherwise the macro will not work.