Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Forcing a Workbook to Close after Inactivity.
Written by Allen Wyatt (last updated June 7, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Dave wonders if he can force a workbook to close after a certain amount of time, provided it is not currently being used. In his office people open workbooks that are on the server and then forget that they are open. When that occurs, nobody else can edit them, so he would like to force workbooks to close if left unattended for 60 minutes.
It is possible to do this using macros, but you may not really want to do that from a business or user-oriented perspective. For instance, let's say that a user has three workbooks open on his system, so that comparisons can be made between them. It is possible to get "tied up" with two of the workbooks for quite a while, with the third one being the one that triggers a shutdown. If your VBA code isn't written correctly, it may end up shutting down whichever workbook has focus at the current time—clearly a result you don't want to occur.
Further, what do you do with unsaved changes when closing? If you save them, you run into the issue that perhaps the user didn't intend to save them. If you don't save them, the converse problem occurs—perhaps there was a lot of data that needed to be saved. You can't have the closing procedure ask if information should be saved; that would keep the workbook tied up as surely as keeping it open (and unused) would.
A possible solution is to simply share or co-author (Excel 2019 and Excel in Office 365) the workbook. If you enable sharing or co-authoring (as discussed in other ExcelTips), then multiple people can have the same workbook open at the same time. If one of those people leaves it open, then nobody else is inconvenienced because they can still open it and, optionally, make changes in the workbook.
If you decide to go the macro-based route, then the solution is rather simple. You need some sort of timer structure (easily implemented through use of the OnTime method) and some way to check to see if someone is doing something in the workbook.
To start, add the following code to a standard macro module. Note that there are three routines to be added:
Dim DownTime As Date Sub SetTimer() DownTime = Now + TimeValue("01:00:00") Application.OnTime EarliestTime:=DownTime, _ Procedure:="ShutDown", Schedule:=True End Sub
Sub StopTimer() On Error Resume Next Application.OnTime EarliestTime:=DownTime, _ Procedure:="ShutDown", Schedule:=False End Sub
Sub ShutDown() Application.DisplayAlerts = False With ThisWorkbook .Saved = True .Close End With End Sub
These three routines are fairly straightforward. The first two respectively turn on the timer and turn it off. Note that these routines utilize the DownTime variable, which is declared outside of any of the routines. In this way its contents can be utilized in multiple routines.
The third routine, ShutDown, is the one that actually closes the workbook. It is only invoked if the OnTime method expires, at the end of an hour. It closes the workbook without saving any changes that may have been made.
The next routines (there are four of them) need to be added to the ThisWorkbook object. Open the VBA Editor and double-click on the ThisWorkbook object in the Project Explorer. In the code window that Excel opens, place these routines:
Private Sub Workbook_Open() Call SetTimer End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call StopTimer End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Call StopTimer Call SetTimer End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Call StopTimer Call SetTimer End Sub
The first two routines are triggered when the workbook is opened and when it is closed; they start the timer and turn it off. The other two routines are executed automatically whenever a worksheet is recalculated or whenever someone makes a selection in the workbook. Both are good indicators that someone is using the workbook (it is not inactively open). They stop the timer and then restart it, so that the one-hour countdown starts over.
There is a downside to using a set of macros such as these: you effectively eliminate Excel's Undo capability. When a macro is executed, the Undo stack is automatically wiped out by Excel. Since macros are running with every change made in the workbook, the person's changes cannot be undone. (There is no way to get around this drawback.)
Another problem is that macros can only be stored in macro-enabled workbooks. Thus, the macro-based solution will not work for regular XLSX files, as they don't allow macros within them. In that case you are limited to non-macro solutions, such as turning on workbook sharing or co-authoring.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8192) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Forcing a Workbook to Close after Inactivity.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the ...
Discover MoreWhile password protecting a workbook does provide some security for the contents in the workbook, if you have several ...
Discover MoreNeed to open a bunch of workbooks from within Excel? It's easy to do when you construct a selection set in the Open ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-07 07:19:39
James Kenneth Button
Maybe just have the windows session go to a hibernate, or sleep mode presenting a logon screen after about 10 minutes of inaction.
OK, leaves the files open - a different action needed for that - maybe closedown apps and the system !
But the timed windows action is one of the basic built-in facilities.
2023-08-25 13:55:01
J. Woolley
@RJ
To save any changes before the workbook is closed, change ShutDown as follows:
Sub ShutDown()
Application.DisplayAlerts = False
With ThisWorkbook
If Not .Saved Then
If .Path <> vbNullString Then .Save Else .Saved = True
End If
.Close
End With
End Sub
This will not save a NEW workbook unless it was previously saved.
2023-08-24 14:54:49
This Macro is EXACTLY what I was looking for. Only one issue, is it possible to Save any changes when the program forceably closes?
2021-12-12 12:15:46
Didier
Thank you for providing such good tips. apart the timer solution I have learned two important things : macros stop Undo and Micro$oft Office 2019 integrates the co-authoring mecanism.
Thanks to @J. Woolley for the optimisation with the dialog box.
2021-11-17 04:26:21
@Don,
I've not tried this particular macro application but I have several macros that run overnight which run automatically and send an report via email ready for me to review the following morning. These run whilst my account is locked, so I don't see why the macros in this article wouldn't do the same.
2021-11-16 09:55:23
J. Woolley
@Don
If the computer is locked, then subsequently unlocked (by the same user), I think everything described here will proceed as intended. Have you tried it?
2021-11-15 10:18:46
Any way to make this work when the computer's locked?
2021-05-01 11:06:31
J. Woolley
I fixed the Popup function's timeout problem described in my last comment. Now you can reliably use it as a timed MsgBox.
2021-04-26 17:27:56
J. Woolley
@Keith
Unfortunately the Popup function's actual timeout is VERY unreliable when used with Excel. The method in my previous comment can be improved by using My Excel Toolbox's freely available Sub
MsgBoxModeless(vPrompt, [vButton], [vTitle], [nFont], [vNextProc])
See https://sites.google.com/view/MyExcelToolbox/
Just modify the Tip's SetTimer, StopTimer, and Shutdown procedures as follows:
Dim DownTime As Date, WarnTime As Date
Sub SetTimer()
DownTime = Now + TimeValue("01:00:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="Warning", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="Warning", Schedule:=False
End Sub
Sub Warning()
WarnTime = Now + TimeValue("00:00:30")
Application.OnTime EarliestTime:=WarnTime, _
Procedure:="ShutDown", Schedule:=True
prompt = vbLf & "This workbook has been inactive for 1 hour. " & vbLf _
& "It will be closed in 30 seconds unless you click Continue."
MsgBoxModeless prompt, , "Warning", , ThisWorkbook.Name & "!Continue"
End Sub
Sub Continue()
Application.OnTime EarliestTime:=WarnTime, _
Procedure:="ShutDown", Schedule:=False
Call SetTimer
End Sub
Sub ShutDown()
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
End Sub
2021-04-25 03:46:34
Keith Farmery
Many thanks for this.
Very useful.
Keith
2021-04-24 13:41:36
J. Woolley
@Keith
My Excel Toolbox includes the freely available Popup function that provides a timed MsgBox:
Popup(Prompt, [Buttons], [Title], [Timeout])
See https://sites.google.com/view/MyExcelToolbox/
Using that function, you could modify the Tip's Shutdown procedure as follows:
Sub ShutDown()
prompt = "This workbook has been inactive for 1 hour. " _
& "It will be closed in 30 seconds unless you click OK."
ans = Popup(prompt, vbExclamation, "Warning", 30)
If ans > 0 Then Call SetTimer: Exit Sub
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
End Sub
2021-04-24 03:46:27
Keith Farmery
Hi
Could this be enhanced by displaying a message on the screen to prompt the user to act, to say that the file has been inactive for "x" minutes and that it will be closed in "y" minutes? I accept this will only work if the user is active on the screen but it would assist with the downfalls of using such a macro.
Thanks
2021-04-23 13:30:24
Venkat Yalamati
Thanks for code sir.. It worked for me!!!
2020-11-14 05:06:45
Hans Hallebeek
HI, I would call the StopTimer the moment you invoke the SetTimer
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments