Time Limit?

[outrider]

Is there a way of setting a time limit on how long an Excel application can be open before it shuts itself down?

Frequently, one of our network users leaves the spreadsheet open and then wanders off, forcing others to keep going to his computer to close the file.

Thanks for your help.

[Richie(UK)]

Hi,

One approach would be to use OnTime. Try this:

In the ThisWorkbook object module:

Private Sub Workbook_Open()
    MsgBox "This workbook will auto-close after 5 minutes 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

And in a general module:

Dim DownTime As Date

Sub SetTime()
    DownTime = Now + TimeValue("00:05:00")
    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

HTH

[Jacob Hilderbrand]

Would sharing the workbook be an option? That way multiple users could have it open at the same time.

Tools | Share Workbook...

[Brandtrock]

Would sharing the workbook be an option? That way multiple users could have it open at the same time.

Tools | Share Workbook...

Outrider - be aware that this limits some of Excel's features. Don't know if this will matter in your specific case, but thought I'd mention it.

HTH,

[Aaron Blood]

I got a couple examples you might be interested in...

This one closes after a set time:
http://www.xl-logic.com/xl_files/vba/shutdown_1.zip

This one closes after a set time of user inactivity:
http://www.xl-logic.com/xl_files/vba/shutdown_2.zip
(might be better for your situation.)

[outrider]

Thanks for all the help.

DRJ, sharing the spreadsheet would be a disaster with some of our operators, some of them are excellent at destroying data and claiming "It was like that when I opened it"

[Ken Puls]

Hi Outrider,

It looks like you've got the answers you're looking for, so I'm going to mark this one solved. Not sure if you are aware that you can actually do that yourself here! It's in your Thread Tools menu at the top of your posts.

If it's not solved, by all means post back though!

[outrider]

The code from Aaron Blood (Shutdown 2) is just what I need, but how do I get it to run as soon as my spreadsheet is opened?.

Thanks.

[Ken Puls]

Hi Outrider,

I've "unmarked" your thread solved now, so that we can get you all fixed up. Sorry about that!

In Aaron's example, there are some required routines in the ThisWorbook module. The relevant pieces to fire it are:

In the ThisWorkbook module
[vba]Private Sub Workbook_Open()
Call SetTime
End Sub[/vba]

And in a Standard module
[vba]Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:00:30")
Application.ontime DownTime, "ShutDown"
End Sub

Sub ShutDown()
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
End Sub[/vba]

What happens is when the workbook opens, the Workbook_Open event fires, calling the SetTime routine. That routine sets the workbook to shut down in 30 seconds.

FYI, the rest of the code stops it from doing it if the user is actively doing something, so it is all required as well.