View Full Version : Auto close excel after 1hr
blastpwr1970
02-13-2007, 06:21 AM
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
Closewb needs to be in a module. How are you going to tell if there is no activity though?
Bob Phillips
02-13-2007, 06:36 AM
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
02-13-2007, 06:37 AM
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
02-13-2007, 08:13 AM
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
02-13-2007, 08:37 AM
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
02-13-2007, 09:08 AM
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
02-13-2007, 06:13 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.