Excel 2007-2010 VBA required to execute a workbook Auto_Close Only when no activities

[chamdan]

Hi,

I have tried different ways as listed on the Net but none of the macro that were given worked the way I wished. The macros execute the auto_close event after 30 minutes even if the there are activities on the workbook, which I am trying not to trigger the Auto_Close event. I could not figure out how to reset the timer to take effect only when there are no activities.

I thought about the Worksheet_Change event but did not know how to reset the timer.

Can any one help me achieve it.

Regards,

Chuck

 

[chamdan]

Solved - 2007-2010 VBA required to execute a workbook Auto_Close Only when no activit

After long hours of search and scratching my brain, I have finally achieved my objective and this works smoothly. I have been testing this for 2 days simulating different situation and all have worked.

Hope this will help other to do similar tasks.

Cheers!

Private Sub Workbook_Open()
' Trigger the timer when the workbook opens
' Sets the timer to 15 minutes
' where "00" Hours ":" "15" minutes ":" "00" Seconds
    EndTime = Now + TimeValue("00:15:00")
    RunTime
End Sub

' Place the following two Worksheet Events in the Worksheet object
Private Sub Worksheet_Change(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:15:00")
    RunTime
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If EndTime Then
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
        EndTime = Empty
    End If
    EndTime = Now + TimeValue("00:15:00")
    RunTime
End Sub

Option Explicit
'---------------------------------------------------------------------------------------
' DateTime  : 09/05/2013 09:00 AM
' Author    : RoyUk
' Amended by: Chuck Hamdan to suit the objective
' Purpose   : Will auto close workbook when no activity. This will allow other to work
'               with the workbook when required.
'---------------------------------------------------------------------------------------
Public EndTime
Sub RunTime()
    Application.OnTime _
            EarliestTime:=EndTime, _
            Procedure:="CloseWB", _
            Schedule:=True
End Sub

Sub CloseWB()
Dim fName As Variant
fName = ActiveWorkbook.Name
    If OtherWBOpened Then
' If multiple workbook opened
' close only this workbook    
        With ThisWorkbook
                .Application.DisplayAlerts = False
                .SaveAs fName
                .Application.DisplayAlerts = True
                .Close
        End With
        Exit Sub
    Else
' Else then close this workbook and close Excel
        With ThisWorkbook
                .Application.DisplayAlerts = False
                .SaveAs fName
                .Application.DisplayAlerts = True
                .Close
                .Application.Quit
        End With
    End If
End Sub

Public Function OtherWBOpened() As Boolean
'This Function will check whether other Workbooks
' are opened as well
Dim wbk As Workbook
Dim i As Integer

For Each wbk In Workbooks
    i = i + 1
Next
If i > 1 Then
' more than one workbook opened
' set it to True
    OtherWBOpened = True
Else
' else set it to False
    OtherWBOpened = False
End If
End Function

Chuck