Excel VBA – Automatically Close an Inactive Workbook

A big problem I’m sure many of you have encountered is being locked out of a workbook saved on your network because someone is in it, or more likely, has opened it and forgotten to close it when they’ve finished. There may well be a message giving you some user ID or some other vague name of the person who is currently in the workbook but you have no idea who that person is, whether they are on your floor or even in the same building. Even if you can work out who they are, chances are they are not at their desk and the computer screen is locked. So you end up simply waiting to see if the workbook is ever freed up and praying that no one else beats you to the released workbook.

So what are your alternatives?

You could opt to have a shared workbook allowing multiple users to access it at the same time. This can work, but someone has to take overall ownership of the workbook to agree changes and any conflicts that may arise within the workbook between users. There are also restrictions applied to some of the functionality within a shared workbook, such as not being able to add or modify data validation rules. Some of the lost functionality will definitely add restrictions that may make the workbook unworkable for many people. Check the Microsoft website for a full list of what you can’t do in a shared workbook (https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-79bd9dee-1aa3-49b5-bc27-a1ad28ffcbce?CorrelationId=4365b78b-4666-4d41-adcb-32c850345466&ui=en-US&rs=en-US&ad=US#__excel_features_that)

Another option is to keep the workbook “un-shared” but make it close itself down after a set period of inactivity. Like that, if someone does walk away from the workbook without closing it or simply has it open despite having finished whatever they were doing, a built in timer detects that nothing has happened and automatically closes the workbook so someone else can get in.

In order to do this we are going to need a bit of VBA code.

We will need 3 routines to start with (the name I will give to each routine is shown in brackets…feel free to call yours whatever suits you best);

1. Start the timer (StartClock)
2. End the timer (StopClock)
3. Close the workbook (ShutDown)

Open the Visual Basic Editor (Alt + F11) and find the workbook you want to automate. Right click on it and select INSERT and then click on MODULE.

Adding a new module

The three routines we need to make all this work need to be created here so they can be called on by event procedures we will create a bit later.

We will need a global variable to store the time each time any of the routines start up. At the top of your module (in General Declarations) declare a PUBLIC variable so it can be recognised anywhere within our project/workbook.

Public NoActivity as Date

The first procedure we will create is to close the workbook and save any changes that have been made to it (you could also choose to not save changes to prevent half-finished data from populating your spreadsheet).

Sub ShutDown ()

Application.DisplayAlerts = False

With ThisWorkbook
.Save
.Close
End with

End Sub

Here we turn off any warning messages in case any pop up, save any changes and then close the workbook. If you don’t want to save any changes you can use .SAVED = TRUE to replace .SAVE.

Sub StartClock ()

NoActivity = Now + TimeValue(“00:05:00”)

Application.OnTime NoActivity , “ShutDown”

End Sub

Here, we populate the variable with the current time plus whatever period you have decided is your limit for inactivity. In this example I’m setting it to 5 minutes.

ONTIME says when we reach now plus 5 minutes, run the SHUTDOWN routine

Sub StopClock ()

On Error Resume Next

Application.OnTime NoActivity, “ShutDown”, , False

End Sub

In this routine, in case anything goes wrong ignore it, and once again if the time limit is reached run the SHUTDOWN routine. The FALSE bit is just there to clear any previously set procedure.

Now that our three routines have been created, we now we need to create some event procedures that will trigger these routines.

If you are unfamiliar with EVENT PROCEDURES, they are routines that start automatically when something happens in the workbook such as the user selecting another sheet. With an EVENT PROCEDURE you are not relying on the user to press a button to run a routine, it simply happens automatically when triggered by an event in the workbook or worksheet.

Double click on ThisWorkbook in the Microsoft Excel Objects folder of your workbook.

Accessing events on ThisWorkbook

Then, from the left drop down box above the script area, select WORKBOOK.

Selecting the Workbook area to add events

The editor automatically adds the OPEN event, which in this case is one of the events we need.

In the event add the code CALL STARTCLOCK;

Private Sub Workbook_Open()

Call StartClock

End Sub

Now, whenever the workbook is opened, the STARTCLOCK routine starts and therefore the timer starts. It may be worth adding a message box here too to warn users that the workbook will automatically close after x minutes of inactivity.

In terms of events that mean the user is doing something in the workbook, we want the following events to restart the timer;

Workbook_SheetCalculate

Workbook_SheetSelectionChange

Might be worth adding this event at worksheet level to stop the timer if people are just browsing in the workbook rather than editing…it is still a form of activity…albeit unproductive!

Worksheet_SelectionChange

And we need a final event to stop the timer just before closing the workbook;

Workbook_BeforeClose

So all our event code will look something like this;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call StopClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_Open()

MsgBox “Please note that this workbook will close,” & vbCr & _
“saving any changes you have made after 5 minutes of  inactivity”, vbInformation, “Warning”

Call StartClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Call StopClock

Call StartClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Call StopClock

Call StartClock

End Sub

Next time you want to post a workbook on a shared drive at work, you now have the following options:

• leave the workbook as it is and hope that people are sensible and close it when they are done
• set it up as a shared workbook, accepting whatever restrictions this imposes
• create a workbook with its own built in shutdown mechanism if nothing happens within a set timescale.

If you want to download a copy of the workbook with this code in it, then follow this link – https://onedrive.live.com/redir?page=view&resid=2C036E200F2C8BCF!255&authkey=!AI887S1-lcOY6RU