Close excel when inactive

[jennie]

Hi,

I would like to have excel close after 15min of being inactive (n
button / mouse clicks, no data entered, etc) however it may still b
the active workbook.

If possible I would like it to bring up a message box that say
something along the lines of:

"About to close, click Cancel to stop." and clicking cancel would kee
the workbook open until it had been inactive for another 15 mi
period.

Does anyone know how to do this?

Thanks
Jenni

 

[Bob Phillips]

You could use Ontime together with worksheet change event code to monitor
it.


There are 5 elements here.


Firstly have a macro which invokes Ontime to run a macro that shutdown in 15
minutes. And you also need a macro to reset the timer . SO, in a standard
code module, add

Option Explicit


Public nShutdown As date

Public Sub SetShutdownTimer()
nSaveWB = Now + TimeSerial(0, 15, 0) ' 15 minutes
Application.OnTime nShutdown, "Shutdown"
End Sub


Public Sub Shutdown()
Dim ans
ans = MsgBox"About to close, click Cancel to stop.", vbOKCancel)
If vbOK Then
Application.Quit
Else
SetShutdownTimer
End If
End Sub

You then need to set the timer in the first place, when the workbook opens.
And you also need to trap any workbook changes , and any worksheet
selectione, so that the timer gets cancelled, and set anew (note this
doesn't
reset the timer for any formatting changes, only data changes).


Option Explicit


Private Sub Workbook_Open()
SetShutdownTimer
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nShutdown, "Shutdown", , False
SetSaveWBTimer
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range) Application.OnTime nShutdown, "Shutdown", , False
SetSaveWBTimer
End Sub



'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



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jennie]

Thanks a lot

I will give it a try

Thanks
Jennie

 

[Benoit Lamarche]

I think it should be:

ans = MsgBox("About to close, click Cancel to stop.", vbOKCancel)

(missing a bracket)

Also, when I try to run the code, I get:

nSaveWB (variable not defined)

and also, after trying to run "Public Sub Shutdown"

I get "Only comments may appear after End Sub ..."

All the code is in the worksheet's property, is that what's intended?