automatically close excel

  • Thread starter Thread starter Mermans Patrick
  • Start date Start date
M

Mermans Patrick

is it possible to automatically close excel after no changes have been made
for a certain time?

thanks in advance

Patrick
 
Patrick,

From a previous post............
If you want, I can send you a workbook with the code.

John

<snip>
Upon opening the workbook, start a timer to detect activity.
Close Workbook after a certain time of inactivity.
Original code was found in Google from Tom Ogilvy.
Modified code per OP request to bring up a UserForm alerting the user
that their time had elapsed and if they chose to continue, the timer would
restart and they could continue working else the workbook would
automatically save and close.


In the workbook Events:

Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As
Excel.Range)
' Check to see if the UserForm Timer is active.
' If it is, it has control, just exit this event without doing anything.
If Timer2Active = True Then Exit Sub
Application.OnTime BootTime, "CloseBook", , False
StartTimer
End Sub

In a regular module:

Public BootTime As Date
Public Timer2Active As Boolean
Sub StartTimer()
' Start the Activity Timer
BootTime = Now + TimeValue("00:00:10")
' If the Activity Timer runs for the specified time
' call the "CloseBook" sub
Application.OnTime BootTime, "CloseBook"
End Sub
Sub CloseBook()
' At this point the main Timer has elapsed.
' Display and let the UserForm take charge.
UserForm1.Show
End Sub
Sub ReallyCloseBook()
If Timer2Active = False Then Exit Sub
Unload UserForm1
ThisWorkbook.Save
' Application.Quit
ThisWorkbook.Close
End Sub

In a UserForm named UserForm1 with one CommandButton named
CommandButton1 (Caption on the CommandButton "Continue Working"):

Private Sub UserForm_Activate()
' Set the Timer2Active Flag to True.
' This stops the SheetSelectionFlag from processing it's code
' This UserForm now has control over what will be done next.
Timer2Active = True
' Start a timer within this UserForm. If the allotted time elapses
' without pressing the CommandButton, call "ReallyCloseBook"
Application.OnTime Now + TimeValue("00:00:10"), "ReallyCloseBook"
End Sub
Private Sub CommandButton1_Click()
' Set the Timer2Active flag to False in order that the
' SheetSelectionChange Event can monitor activity.
Timer2Active = False
' Restart the Main timer again
StartTimer
' Dispense with the UserForm
Unload UserForm1
End Sub
<snip>
 
thanks a lot

by the way , i kept looking around on google and found some usefull stuff
concerning the application.ontime event

Patrick
 
Back
Top