Automatically Closing a Workbook

If you work in a security-conscious environment, you may always be looking for ways to increase the security of your system, even without any effort on your part. One way you can increase security is to add some macro coding to your workbook that results in it being closed if you don't make any changes to a worksheet within any ten-minute period. For instance, if you step a way from your computer, then the workbook you were using is automatically closed after 10 minutes.

The following series of three macros will implement just such a system. The first macro (Workbook_Open) is executed when a workbook is first opened. It uses the run_time subroutine, which uses the OnTime method to specify that ten minutes in the future the close_wb macro will be run.

The second macro (Worksheet_Change) is triggered whenever you change something in a workbook. When this occurs, the same run_time subroutine is used to again specify that close_wb should be run ten minutes in the future.

Private Sub Workbook_Open()
    close_time = Now + TimeValue("00:10:00")
    run_time
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If close_time Then
        Application.OnTime _
          EarliestTime:=close_time, _
          Procedure:="close_wb", _
          Schedule:=False
        close_time = Empty
    End If
    close_time = Now + TimeValue("00:10:00")
    run_time
End Sub

Public close_time
Sub run_time()
    Application.OnTime _
      EarliestTime:=close_time, _
      Procedure:="close_wb", _
      Schedule:=True
End Sub

Sub close_wb()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Saved = True
        .Close
    End With
End Sub

If the close_wb macro is ever executed, any changes to the current workbook are discarded, and the workbook is closed.