Idle timeout for Excel? 1

[artsi03]

Is it possible to add a idle time-out to Excel-sheet? If someone opens a document and won't make any changes there for let's say 30 minutes, the macro would save all and exit. The script would need to run immediately from startup.

Thanks.

 

[TonyJollans]

Hi artsi03,

Yes, it can be done. You need to use Application.OnTime along with Events which fire on activity (possibly Workbook_SheetSelectionChange). I suspect if you search the forum here you can find some code but, if not, I can knock some up for you.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[artsi03]

Tony,

I tried to find some code here, but was unsuccesful. Could you help me out with this.

Thanks
Artsi

 

[TonyJollans]

Hi Artsi,

This is fairly basic. What it does on startup is to set a routine to fire in 30 minutes. Then, whenever the selection is changed that routine is cancelled and a new one set up to run half an hour later. After half an hour of no activity in the workbook the routine will be run by Excel and it closes the workbook.

A couple of points:

- if you spend half an hour working on code in the VBE without doing anything in the workbook proper it will suddenly disappear before your very eyes. I don't know of any easy way to address this.

- the close routine only closes the workbook so other workbooks are not affected. If this is the only workbook open it could be changed to close the application as well.

In the ThisWorkbook moduleof the workbook, add this code:

[blue]Dim Scheduled_Time As Date

Private Sub Workbook_Open()
    Schedule_Inactivity_Close
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.OnTime Scheduled_Time, "Inactivity_Close", , False
    Schedule_Inactivity_Close
End Sub

Private Sub Schedule_Inactivity_Close()
    Scheduled_Time = Now() + TimeValue("00:30")
    Application.OnTime Scheduled_Time, "Inactivity_Close"
End Sub[/blue]

and in a standard code module, also within the workbook, add this:

[blue]Sub Inactivity_Close()
    MsgBox "Closing"
    ThisWorkbook.Close True
End Sub[/blue]

That should be all you need.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[artsi03]

Thanks, this really helped a lot!