Solved

Excel 2010:  Macro to Automatically Close Out of Document

Posted on 2014-02-06
4
1,277 Views
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.
0
Comment
Question by:itsmevic
4 Comments
 
LVL 8

Assisted Solution

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

application.Quit

Open in new window

0
 
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 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
        .Close
    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

0
 
LVL 80

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:itsmevic
ID: 39848796
Great feedback!  Thank you everyone!!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now