Go Premium for a chance to win a PS4. Enter to Win


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

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 Tips.net 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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