PDA

View Full Version : [SOLVED] Time Limit?



outrider
12-21-2004, 02:13 AM
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)
12-21-2004, 02:17 AM
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
12-21-2004, 03:39 AM
Would sharing the workbook be an option? That way multiple users could have it open at the same time.

Tools | Share Workbook...

Brandtrock
12-21-2004, 10:54 AM
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
12-21-2004, 03:28 PM
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
12-22-2004, 07:23 AM
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
12-22-2004, 01:43 PM
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! :yes 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
12-23-2004, 05:06 AM
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
12-23-2004, 09:36 AM
Hi Outrider,

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

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

In the ThisWorkbook module
Private Sub Workbook_Open()
Call SetTime
End Sub

And in a Standard module
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

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.