Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Forcing a Workbook to Close after Inactivity

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Forcing a Workbook to Close after Inactivity.

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. Excel's VBA isn't terribly discriminating—when a workbook is closed, it is typically the one which has focus at the current time.

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.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2281) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Forcing a Workbook to Close after Inactivity.

Related Tips:

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Nicolae    06 Nov 2016, 03:49
Thank you,
this code in VBA, was of great help to me!
M.v.K.    26 Oct 2016, 10:52
Thanks, it was very helpfull.
The macro works perfect.
Ivor Shaer    17 Sep 2016, 13:29
I want to vba to automatically close any file/workbook, but not the active workbook (which will contain the VBA), if the file name starts with "IP_" if no changes have been made to that filefor ten minutes.

Ivor Shaer    16 Dec 2015, 02:25
All my macros reside in a MacroFile which creates, edits and amends other workbooks. Included in the MacroFile is a form which opens existing files which a user will want to edit or view.

Is it possible to have the MacroFile identify the file that is being opened, the time it is opened and then close it if it is inactive for, say 10 minutes utilising code in the MacroFile?

I could programmatically add the time modules you have given to each workbook, but would prefer if the code resided in the MacroFile
Tyson    27 Jan 2015, 15:02
is there a way to create a visible countdown using the application.statusbar code?
for the remaining time left on the hidden countdown.
Andrew McQuillen    28 Nov 2011, 08:04
Thiis is a fantastic tip. I am always leaving the same file open and getting told to shut it down. As i always want to save the changes i have modified my version to save when it is closed.
 
Ray Austin    27 Nov 2011, 17:06
In this scenario you are working on a company server. So is it not possible to ask the server who is using the file ?
I have seen this previously on other programmes but do not know how it is done.
awyatt    26 Nov 2011, 09:38
Sure. A safer alternative is to ask. However, the problem is that the person is away from their computer, and you want to free the workbook for others to use it.

What if they are gone for the weekend, but left it open on their computer? How do you ask them? That is part of the problem with coming up with any solution--you need to have the program make a decision whether to save or discard changes.
Ray Austin    26 Nov 2011, 04:37
A safer alternative ?
Is it possible to have a routine which identifies the forgetful user, so you can ask them to close and not have to worry whether changes should be saved ?
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.