Auto shut down of spreadsheet if inactive.

[Hoopsah]

Hi,

I know this is being a wee bit nasty, but still it is something that I need to do.

I have created a spreadsheet to collate the absence levels within my department.

I made this file open to anyone who knows the password.

What I have now found is that some people go into the program, do their stuff and then walk away leaving it active on their screen. Screensaver will then kick in which is password protected.

I don't want to actively share the workbook as I don't feel that it works properly for what I am doing.

So heres the deal - is there a way I can build in an automatic shutdown of the program/spreadsheet if the user hasn't touched it for a period of time?

I would be nice and try set it for at least half an hour, but it would have to save and exit the program.

Any takers?

Hoopsah

[Bob Phillips]

[vba]

Option Explicit

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 30, 0) '30 minutes
'start a timer to countdown inactivity
Application.OnTime Now + nElapsed, "Closedown"
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'-----------------------------------------------------------------
'close the timer
Application.OnTime Now + nElapsed, "Closedown",,False
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime Now + nElapsed, "Closedown"
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code



Put this code in a standard code module

[vba]

Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Closedown()
'-----------------------------------------------------------------
ThisWorkbook.Close SaveChanges:=True
End Sub
[/vba]

[Hoopsah]

Hi Bob,

sorry about the delay on this one but I have been away from my desk for a couple of weeks.

Will try it out soon and let you know how I got on,

Cheers

Gerry