VBA Express : Excel - Closing an inactive workbook

Excel

Closing an inactive workbook

Ease of Use

Easy

Version tested with

2003

Submitted by:

Paleo

Description:

This macro saves and closes an inactive workbook after 30 minutes.

Discussion:

Sometimes a user leaves his/her desk and leaves some sensitive data at his/her workbook exposed. This macro makes sure if there is no activity on the workbook for 30 minutes it's automatically saved and closed.

Code:

instructions for use

			 ' ----- This goes to the This_Workbook
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 
    Call Timer 
End Sub 
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
    Call Timer 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call Limpa 
End Sub 
 
 ' ----- This goes to the Module
Public vartimer As Variant 
 
Sub Timer() 
    Call Limpa 
    vartimer = Format(Now + TimeSerial(0, 30, 0), "hh:mm:ss") 
    If vartimer = "" Then Exit Sub 
    Application.OnTime TimeValue(vartimer), "Fecha" 
End Sub 
Sub Fecha() 
    With Application 
        .EnableEvents = False 
        ActiveWorkbook.Save 
        .Quit 
    End With 
End Sub 
Sub Limpa() 
    On Error Resume Next 
    Application.OnTime earliesttime:=vartimer, _ 
    procedure:="Fecha", schedule:=False 
    On Error GoTo 0 
End Sub

How to use:

Copy the Private Sub Workbook_SheetActivate(ByVal Sh As Object), Private Sub Workbook_BeforeClose(Cancel As Boolean) and Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
Press Ctrl + R to show the Project Explorer.
Double-click the This_Workbook
Paste the code into the right-hand code window.
Copy the Public declaration, Sub Timer(), Sub Limpa() and Sub Fecha() code above
Insert a new module (click on the menu bar at Insert than select Module)
Double-click the new module
Paste the code into the right-hand code window.
Close the VBE and save the file
Re-open the file and its done

Test the code:

Open the workbook and it's running already. You may have 30 minutes of inactivity at the sample file.

Sample File:

ClosingWorkbook.zip 7.64KB

Approved by mdmackillop

This entry has been viewed 280 times.