Pearson Software Consulting Services

    Timed Closing Of A Workbook 

         You may find that your application should close a file if there has been no user activity on that workbook for a specified period of time. That is, if the user hasn't accessed the open workbook for so many minutes, save and close the workbook. This can be achieved using event procedures and the OnTime method.

In a standard module of the VBA project, paste the following code:

Public RunWhen As Double
Public Const NUM_MINUTES = 10

Public Sub SaveAndClose()
    ThisWorkbook.Close savechanges:=True
End Sub


Change the value of
NUM_MINUTES  to the number of minutes you want to leave the workbook unattended before closing.

In the ThisWorkbook module, paste the following code:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
    ByVal Target As Range)

    On Error Resume Next
    Application.OnTime RunWhen, "SaveAndClose", , False
    On Error GoTo 0
    RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
    Application.OnTime RunWhen, "SaveAndClose", , True

End Sub

 

This code cancels and reinstates an OnTime event whenever a cell value is changed or when the selection is chagned. If neither changes in the specified amount of time, the workbook will save itself and close.

 

 

 

     
     

 

Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson