Excel 2010:  Macro to Automatically Close Out of Document

Posted on 2014-02-06
Last Modified: 2014-02-10
Wondering if anyone has a macro that they can share that can automatically Save and Close out of an Excel Spreadsheet after a period of inactivity.   This would help greatly, especially for those that forget to close out of the spreadsheet, leaving others at their mercy until they do.  A lot of the times the user will leave it open, leave for the day and we can't use it unless of course we save a local copy of it, which doesn't work.
Question by:itsmevic
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Assisted Solution

TheNautican earned 20 total points
ID: 39839886
In excel its, I believe.


Open in new window

LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 80 total points
ID: 39839908
It is possible with VBA, but there are also drawbacks to using this type of solution:

1) Are you sure that the changes are meant to be saved?  If you force a save, you are also forcing any testing/changes that were not intended to be saved by the user.  With that said, if you skip the save operation, you can lose hours worth of data.
2) You lose the ability to Undo any changes after a macro is fired (the timers for example).
3) Macro objects can only be saved in .xlsm format and also present dialogues to the user each time about enabling the document, etc.  For larger organisations, this can be a pain.

I would suggest moving more to a "shared" workbook model, but at any rate, this is still possible.  Allen Wyatt from has the following examples.

The following three codes are inserted into a standard module.  the first two control the running of the timer (on and off), the third is the shutdown option (with save):
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
    End With
End Sub

Open in new window

To control the timer interval, change the following line:
    DownTime = Now + TimeValue("01:00:00")
to a value that suits your needs.  "01:00:00" is 1 hour (60 minutes).

Next, you would need to add the following codes to the ThisWorkbok object in order to call the previous procedures.  The first code triggers when the workbook is opened and starts a timer, the second captures the workbook close event and trigger the timer on and off codes.  The third and forth codes look for any changes to the workbook, and then stop and restart the timer.

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

Open in new window

LVL 81

Accepted Solution

byundt earned 400 total points
ID: 39839969
Code for this purpose and a sample workbook was posted in this PAQ: http:/Q_28202548.html#a39378502. As posted, after 10 minutes of inactivity a modeless userform is displayed with a warning message. If the user dismisses the userform, he gets another 15 minutes. If not, the workbook is saved and Excel closes in 5 additional minutes.

I do not suggest converting the workbook to a Shared workbook. These have proven problematic over the years when multiple people are accessing the workbook, making changes and saving it. File corruption is a frequent complaint in such situations.

Author Closing Comment

ID: 39848796
Great feedback!  Thank you everyone!!

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question