timeout on use - save and close down automatically

[hennahairgel]

We've got a excel workbook that does various natty things at work, but there is a tendency for one my colleagues to open the workbook, and leave it open for most of the day, meaning that no one else can use it without asking her to close the book down.

Is there a way of timing how long it is since the book has last been editied, and if it has not changed for the last, say 10 minutes, save and close the workbook?

I don't have the faitest clue where to start with this one!

TIA,

Henry

 

[Damon Ostrander]

Hi Henry,

It's a little involved to explain, so I'm sending you an email with a sample file attached. This one closes the file in 30 seconds (for checkout purposes). Just change the Duration constant in the macro code to make it 10 minutes.

One potential problem with this is that the workbook won't time out unless the user enables macros. There is no way to keep the user from disabling macros other than making the workbook unusable without macros enabled. If your workbook does not already have macros that are essential to its usability your will probably need to do something like hiding the key worksheets and using a macro to unhide them--thereby making the workbook unusable unless macros are enabled.

Damon

 

[queenbuc]

I would like a copy of this macro if you will please...

 

[Ivan F Moala]

I would like a copy of this macro if you will please...

Hi queenbuc

As this Post was done a while ago...if you don't get a reply then
send a PM to Damon or email him.
Otherwise repost and I'm sure someone will help.

 

[Iridium]

Saved this from the board ages ago but can't fing the thread - anyway try this

Timing out a Workbook
Is there a way to set up a workbook so that if a user leaves it open and walks away from his/her machine, Excel will save the changes and close the workbook?

In my world, various people, across many states, open and edit some of the same workbooks via a network setup. We have not shared the workbooks because of some of the limitations that sharing causes. On a good day the user opens the workbook, makes the changes that are necessary, saves his/her changes, and closes the workbook thus allowing the next user to open it. However, we know every day is not a good day - on these "not so good days" days the user opens the workbook, makes some changes, gets distracted by a donut drop off, and walks off to investigate leaving the workbook open. Now the person is ATL whois trying to open it to make changes can only open as a READ-ONLY...

Does anyone have any suggestions?
_________________
Beth

13 Mar 2003 16:52


Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 1428
Location: Worcester, England

Hi Beth,

See if this does what you need - it closes after 20 seconds of inactivity (defined in this case as no calculation or change of selection).

In a general module:

code:
--------------------------------------------------------------------------------
Dim DownTime As Date

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



In the ThisWorkbook object:
code:
--------------------------------------------------------------------------------
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

 

[Smitty]

This isn't significant, but it's been hammered into me to compress where you can

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

Could be

Sub ShutDown() 
   ThisWorkbook.Close True 'Or ActiveWorkbook
End Sub

Smitty

 

[ExHell]

Place this in a standard Module

Dim Lasttime As Double
Dim Thistime As Double

Sub auto_open()
Application.OnEntry = "zerotime"
Lasttime = Now
Thistime = Now
CounterTime
End Sub

Sub auto_Close()
Application.StatusBar = False
Application.OnEntry = ""
If Thistime < TimeSerial(0, 5, 0) Then
ThisWorkbook.Close True
End
End If
Application.OnTime Now() + TimeSerial(0, 5, 0), procedure:="countertime", schedule:=False
ActiveWorkbook.Close SaveChanges:=True
End Sub

Sub CounterTime()
Thistime = Now - Lasttime
Application.StatusBar = "Unused for " + Format(Thistime, "hh.mm.ss") + ". Closes in 00.05.00"
If Thistime > TimeSerial(0, 5, 0) Then
ThisWorkbook.Close True
End
End If
Application.OnTime Now() + TimeSerial(0, 0, 1), "countertime"
End Sub

Sub Zerotime()
Lasttime = Now()
End Sub


And this in a worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Zerotime
End Sub

 

[tusharm]

For three different takes on this issue see
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=6
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=7
and
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=8