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

chamdan

New member
Joined
Mar 22, 2013
Messages
28
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2013
Messages
28
Reaction score
0
Points
0
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!


HTML:
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
HTML:
' 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

HTML:
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
 
Top