Closing sheet on time-out

worldofrugs

New Member
Joined
May 17, 2010
Messages
5
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 ;)
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

quetions

Board Regular
Joined
Feb 11, 2010
Messages
88
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

Code:
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

Code:
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

New Member
Joined
May 17, 2010
Messages
5
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 :rolleyes:)

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..

 
Last edited:

worldofrugs

New Member
Joined
May 17, 2010
Messages
5

ADVERTISEMENT

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!!! :pray:

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

quetions

Board Regular
Joined
Feb 11, 2010
Messages
88
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
 
Last edited:

worldofrugs

New Member
Joined
May 17, 2010
Messages
5

ADVERTISEMENT

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 :p)
 

quetions

Board Regular
Joined
Feb 11, 2010
Messages
88
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

New Member
Joined
May 17, 2010
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,293
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top