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:

  1. 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.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. Press Ctrl + R to show the Project Explorer.
  5. Double-click the This_Workbook
  6. Paste the code into the right-hand code window.
  7. Copy the Public declaration, Sub Timer(), Sub Limpa() and Sub Fecha() code above
  8. Insert a new module (click on the menu bar at Insert than select Module)
  9. Double-click the new module
  10. Paste the code into the right-hand code window.
  11. Close the VBE and save the file
  12. Re-open the file and its done
 

Test the code:

  1. 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 266 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express