I need to set some type of function that will save & close a spreadsheet if it is left unattended for a certain amount of time.
Is there an option for this or do i need to create a macro?
Any help would be great. Thanks.
I need to set some type of function that will save & close a spreadsheet if it is left unattended for a certain amount of time.
Is there an option for this or do i need to create a macro?
Any help would be great. Thanks.
There is no option within Excel to automatically time out. Windows itself has a time out facility - by using a screen saver and setting the on resume display welcome screen item (in XP) you can force users to re-login when there has been no activity on the PC.
For Excel in particular, you could set up code to detect when the Workbook has been inactive. Try the following ....
Copy and paste the following into the ThisWorkbook module -
This simply checks whenever the workbook is active and resets a timer.![]()
Option Explicit Private Sub Workbook_SheetCalculate(ByVal Sh As Object) ResetTimer End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ResetTimer End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) ResetTimer End Sub
Then copy and paste the following into a user module -
Change the green highlighted part "00:00:30" above to whatever interval you need (currently this is set for 30 seconds).![]()
Public CloseDownTime As Variant Public Sub ResetTimer() On Error Resume Next If Not IsEmpty(CloseDownTime) Then Application.OnTime EarliestTime:=CloseDownTime, Procedure:="CloseDownFile", Schedule:=False CloseDownTime = Now + TimeValue("00:00:30") ' hh:mm:ss Application.OnTime CloseDownTime, "CloseDownFile" End Sub Public Sub CloseDownFile() On Error Resume Next Application.StatusBar = "Inactive File Closed: " & ThisWorkbook.Name ThisWorkbook.Close SaveChanges:=True End Sub
The Application.StatusBar = item is optional - it simply leaves a message for the user at the bottom of the Excel screen, which will remain until Excel is closed down and re-started.
Last edited by Loz; 05-02-2007 at 05:05 AM.
I can only get it to it the first time.. ??
Mettegaga, welcome to the forum
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks