Closing sheet on time-out

[worldofrugs]

I have some excel sheets that we're using to lookup vendor information, that is not ment to see by customers/visitors.
As these files sometimes are opened on computers on out sales floor and occasionally are left open by our sales staff, I was wondering the following:

Is it possible to have a timeout set to an excel sheet?

Example:
User opens the document. When the user does not do anything (like scrolling / swithching prorgams etc.. --> with other words: Computer is idle!) for 5 mins, I like to document to be closed automatically.

1) is this at all possible?
2) if it is, I would assume this goes via a macro / coding. I have no knowledge at all with coding, could someone help me on this?

TIA

 

[Andrew Poulsom]

Look here:

http://www.cpearson.com/Excel/TimedClose.htm

 

[quetions]

yes it is possible, and yes it is a macro. here is a working version of what you are asking for.

in the "thisworkbook" area paste the following

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

then create a module and paste the following

Dim DownTime As Date

Sub SetTime()
DownTime = Now + TimeValue("00:05:00") '<-- change the amount of idle time here
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

 

[worldofrugs]

Wow!!
That was a fast reply from both of you!
Thanks for that link, from what I'm reading there it is exactly what I'm looking for (and does not look to complicated for this coding noob )

1 more question however...
I assume I have to enter this code for each document (that's fine), but I'm wondering:
Does it automatically run when opening the document?

"playing with it right now, to see if this dummy can get it to work...
I will post the outcome..

 

[worldofrugs]

As promised an update:
I used the code from 'quetions' to begin with and it works awesome!
However, scrolling in the document or using the pc for something else, does not stop the 'count-down'. (computer-idle time).
It would be perfect if this was the case.
Not sure if this would make this to complicated/implement?

Like I mentioned, this would work 'as-is' but would prefer the 'pc-idle' time.
Next I will try out the code given by 'Andrew Poulsom', to see if this has a different behavior on it.

Thanks so much guys for your fast replies and your help!
You guys ROCK!!! ray:

------- EDIT-----
Also tried the code from 'Andrew' --> has exactly the same result.

 

[quetions]

Excel for some reason terms idle time as anything but actual cell manipulation. What this means is that it does not count scrolling, viewing, etc inside or outside of excel as an actual action. The nice part of the code I posted is that you can adjust the time, up or down as needed. If there is another solution that actually interprets mouse movement as non-idle time for excel, I am afraid I don’t know it. Maybe there is someone else that does as there are a ton of excel gurus out there.


edit: to answer your other question, the code I provided does not need to be entered in every worksheet, just to the location I referenced.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[worldofrugs]

Thanks for clearing that with your detailed explenation 'quetions'!
As mentioned, it will work 'as-is', so not oo worried about it. It just would have been the 'ultimate' solution..

to answer your other question, the code I provided does not need to be entered in every worksheet, just to the location I referenced.

I'm not sure what you try to explain here. (remember I'm the dummy with this stuff )

 

[quetions]

that was my mistake, I read your question wrong, I thought you were asking about each sheet in the workbook, not differing workbooks....so now that i have read your question a second time, yes in each workbook.

 

[worldofrugs]

That makes it all clear.
Thanks for the reply and all your help!

To others:
If someone still has a (rather simple) suggestion for using the pc-idle time-out, please let us know. TIA!