Auto Close Workbook.

[adam]

I'm using following code to auto close my workbook after some time its idle. But the code does close the workbook even if the user is active. Any help to sort this would be kindly appreciated.

Code: Select all

Option Explicit

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationAutomatic
Application.EnableCancelKey = xlDisabled
Sheets("HomePage").Activate
Worksheets("HomePage").Protect UserInterfaceOnly:=True
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHorizontalScrollBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Call StartTimer
Range("e5").Select
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

Code: Select all

Option Explicit

Public RunWhen As Double
Public Const cRunWhat = "CloseWorkbook"

Public Sub StartTimer()
    RunWhen = Now + TimeValue("00:20:00")
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Public Sub CloseWorkbook()
    If Workbooks.Count > 1 Then
        'More than 1 workbook is open so close only this workbook
        ThisWorkbook.Close SaveChanges:=False
    Else
        'Only 1 workbook is open so close the Excel app, which also closes this workbook
        Application.Quit
    End If
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

[HansV]

Add the following code in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Call StopTimer
   Call StartTimer
End Sub

This will reset the timer each time the user edits a cell. If you also want to reset the timer when the user selects a different cell (or range), add

Code: Select all

[code]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Call StopTimer
   Call StartTimer
End Sub

[adam]

I have forms in my workbook where users will most of the time use forms to enter data to sheet and Microsoft Access tables. Will the code work for them too?

[HansV]

Would you like the timer to be suspended while a userform is open?

[adam]

Yeah. If there's inactivity after opening the form.

[HansV]

Are all userforms modeless?

[adam]

Yes.

[HansV]

You could use the MouseMove event of the userforms to reset the timer when the mouse is moved over the userform. You'll have to add the code to each of the userforms:

Code: Select all

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call StopTimer
    Call StartTimer
End Sub

[adam]

Thanks for the help Hans. Much appreciated.