Close Workbook After 10 Minutes

[Taffy]

What I would like to do is if there has been no activity on the workbook for
10 minutes it will save and close automatically. Any pointers most
appreciated.

Taffy

 

[Paul B]

Taffy, here is a macro that will do it, I don't remember who wrote it, maybe
somebody will see it and let me know who to give credit to.
As is it will save and then close the workbook after 20 seconds of
inactivity, use to test then change the 20 seconds to your time

'put this in a standard module
Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:20") 'change time as needed
Application.OnTime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown",
Schedule:=False
End Sub
'*******************************************

'put this in thisworkbook
Private Sub Workbook_Open()
MsgBox "This workbook will auto-close after 20 seconds of inactivity"
Call SetTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Excel.Range)
Call Disable
Call SetTime
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Dave Peterson]

Paul B. gave you code to do what you asked, but I'd be a little careful
implementing this.

If I open a workbook and make a bunch of changes and realize that I don't want
to save the changes, you're code will save it when it closes. This could be a
mild irritant or a big old problem--depending on how much stuff was lost.

And closing without saving represents the opposite end of the spectrum. If I
spend 8 hours making changes without saving and your code closes without saving,
you might have a disgruntled user chasing you.

Whatever you do, make sure your users know what's going to happen.

 

[Taffy]

Paul B thanks, does exactly what it says on the tin.

Dave, thanks for the comments. The workbook is being used by three
different people in different buildings. They only have to enter a number
and print of a chart each day, should hear the moaning when one of them
leaves the workbook open.

More help

Is it possibe to have a message box open 30 seconds before closing the
workbook MsgBox "This workbook will auto-close in 30 seconds"
with a OK and Cancel button with a timer ticking down?

This message box should come to the front of all open windows.

Or should this be in a new post?

Thanks Taffy

 

[Dave Peterson]

You could do this code (from Jim Rech):

Sub SelfClosingMsgBox()
CreateObject("WScript.Shell").Popup "Hello", 30, _
"This closes itself in 30 seconds"
End Sub

I tested it by running this:

Sub aaa()
Application.OnTime Now + TimeSerial(0, 0, 20), "selfclosingmsgbox"
End Sub



It popped up over other windows in my test--win98, though. I'm not sure how all
versions of windows will handle it.