Auto close excel after 1hr

[blastpwr1970]

Hi,

Could somebody help me, I been trying to make excel to close and save the workbook automatically after 1 hr if there is no activity.
The problem I am having is that it runs the fisrt part and then it tell me that it can not find my sub macro (CloseWB).
Help

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "CloseWB"
End Sub

Sub Closewb()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

[moa]

Closewb needs to be in a module. How are you going to tell if there is no activity though?

[Bob Phillips]

The Closewb procedure should be in a standard code module, not in the Thisworkbook class module.

But where is there anything there that stope the closedown if there is activity. You should use code like this



Option Explicit

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

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime Now + nElapsed, "Countdown"
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



Put this code in a standard code module



Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Countdown()
'-----------------------------------------------------------------
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

[johnske]

As mentioned, Closewb has to be in a standard code module, not in the ThisWorkbook code module. Have a look here for more ideas regarding this type of thing... http://www.vbaexpress.com/kb/getarticle.php?kb_id=516

[blastpwr1970]

Thanks for the rapid response.
I got little problem do.
When I making changes the workbook still closes by the time specified it does not reset the timer.

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


Everything else worked great.
Thank you again

[mvidas]

You can always do it the fun (read: over-complicated) way, using an API to test if the application has been used in a given amount of time. I have set this currently at 10 seconds of idle, just takes changing the const value to fix that though

The only downfall to it now is that when you open it, the toolbars are greyed out for some reason. Right-clicking anywhere in the toolbar will fix that though, I just haven't explored it fully to fix it

Matt

[Bob Phillips]

Sorry missed an important bit



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

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime nTime, "Countdown", , False
nTime = Now + nElapsed
Application.OnTime nTime, "Countdown"
End Sub


and



Option Explicit

Public nElapsed As Double
Public nTime As Double

Public Sub ProcessData()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Next i

End Sub

Public Sub Test()

End Sub

'-----------------------------------------------------------------
Sub Countdown()
'-----------------------------------------------------------------
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

[johnske]

Shouldn't you also cancel the scheduled procedure on close (for when the user closes it before the elpsed time expires)? e.g. Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
Application.ontime EarliestTime:=nTime, _
Procedure:="Countdown", Schedule:=False

End Sub