Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

Forcing a Workbook to Close after Inactivity

by Allen Wyatt
(last updated September 12, 2015)

23

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 the workbook. If you enable sharing (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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8192) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Forcing a Workbook to Close after Inactivity.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Elapsed Days as Years, Months and Days

Need to know how many days there are between two dates? It's easy to figure out—unless you need the figure in years, ...

Discover More

Different Layout for a Portion of a Page

Got a document layout that requires a portion of the page to be in one layout and another portion to be in a different ...

Discover More

Changing Shortcut Properties

Want to change how a shortcut behaves when you double-click it? Just display the Properties dialog box for the shortcut and ...

Discover More

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!

More ExcelTips (ribbon)

Shrinking Workbook Size

As you work with a workbook over time, it is possible for the workbook to grow to a huge size. If you want to shrink the size ...

Discover More

Remembering Commonly Used Workbooks

Want a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.

Discover More

Using a Single Password for Multiple Workbooks

While password protecting a workbook does provide some security for the contents in the workbook, if you have several ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 1?

2017-02-03 05:06:37

Barry

Personally, I make sure that there are backups either of the original workbook if changes are saved, or of the workbook with the unsaved changes if changes are not otherwise saved. That there is always a way to recover whatever happens.


2017-02-03 01:40:46

moen

Dear sir
when i use these codes they show that the compile error invalid outside procedure: Call SetTimer


Please help me or send me the file including this macro.


2016-11-08 08:10:42

Chris

Without more details, try turning on Designer Mode in the Developer tab. If you can save it with that on, then you've got something in your worksheets before save sub preventing it.


2016-11-07 05:02:46

Mark

The procedure works fine for me to shut the workbook down but for some reason when using the workbook I am not able to save it manually using the save icon or use any of the items on the menu bar.
Any ideas why ?


2016-10-14 06:47:00

Ron

Thank you for your answer. I'll test it!


2016-10-13 11:32:57

Chris

Hi Ron, to save the workbook, just change the .close SaveChanges parameter to True:

Sub ShutDown()
Application.DisplayAlerts = False
ThisWorkbook.Close SaveChanges:=True
End With
End Sub

Make sure you save redundancy backups, as accidental changes can overwrite a whole lot of wotk.


2016-10-12 03:33:27

Ron

Hello,

perfect script for me. I only have one question. Now it's closing without saving te file, is it possible to change the script in a way that excel will save the file before closing?

Thanks in advance!

Ron


2016-06-30 07:45:15

Munusamy

replace the Procedure = "ShutDown",

to Procedure := "ShutDown",


correct code is:

Sub SetTimer()
DownTime = Now + TimeValue("00:00:15")
Application.OnTime EarliestTime:=DownTime, _
Procedure := "ShutDown", Schedule:=True


2016-05-18 12:21:16

Spencer

Rick Swartz 13 Oct 2015, 03:16
@Jordan Champine There is a colon missing in front of the equals sign behind the word procedure.

the colon is missing in the actual article


2016-04-20 11:25:23

Donald

How would I change the code so that it DOES save the workbook before closing?


2016-04-04 11:14:35

Donald

Thanks Rick - that did it!


2016-04-02 05:08:01

Rick Swartz

@Donald see my comment from 13. Oct. 2015


2016-04-01 10:50:23

Donald

When I open the workbook, I get

Compile Error:
Syntax error

On the following piece of code:

Sub SetTimer()
DownTime = Now + TimeValue("00:00:15")
Application.OnTime EarliestTime:=DownTime, _
Procedure = "ShutDown", Schedule:=True


2016-02-04 06:10:23

Barry Wallace

Sharing a workbook would be a simple way around this. However, when you share a workbook you loose several features among them the ability to sort cells by color.

I developed a series of Excel files that my department uses to track our work. One of the key features is the ability to sort by color; without it, a very important feature is lost.


2016-02-01 12:06:03

rafael

This is not resetting the timer in my case... it executes the macro although doesn't do nothing


2016-01-07 14:39:04

Lawrence

@ Barry,

Is there a way to make this macro save the workbook before closing, without having it reopen after the given amount of time (1 hr in this example)?

Whenever I change the code to save before closing, I run into the problem guy faced where the workbook continually reopens, then closes after the given time amount.


2015-12-14 19:07:37

Matthew James

Thank you so much for this!


2015-11-03 05:13:46

Barry

@Guy,

This is because the "Ontime" function is still active until Excel itself is closed.

To deactivate the OnTime function you need to call the Ontime function in the Workbook_BeforeClose event with all the same parameters except the Schedule:=False


2015-11-02 18:10:49

guy

When other excel workbooks are open, and I close the workbook of interest, it reopens again after the downtime amount of time. help !


2015-10-13 03:16:25

Rick Swartz

@Jordan Champine There is a colon missing in front of the equals sign behind the word procedure.


2015-10-12 15:27:56

jordan Champine

I tried the code and get "Compile error: Syntax error"

this portion of code is highlighted in red...

Application.OnTime EarliestTime:=DownTime, _
Procedure = "ShutDown", Schedule:=True

any ideas?


2015-09-21 18:41:49

Don Pierce

Never mind. I determined that one of the functions was defined in a different module.
I simply changed the function name to Set_time and it worked fine
Thanks


2015-09-21 18:21:54

Don Pierce

I tried to use your code to force closure of Excel 2010. When I start it I get a debugger error
Compile Error:
Ambiguous name detected: ~

The Call StopTimer is hightlighted in the following

Private Sub Workbook_Open()
Call SetTimer
End Sub

I have worked with macroros before so this is a new one on me.
Thanks


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.