Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Excel 2010:  Macro to Automatically Close Out of Document

Posted on 2014-02-06
Medium Priority
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 80 total points
ID: 39839886
In excel its, I believe.


Open in new window

LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 320 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 1600 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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