Skip to content

How to Close the Workbook After a Given Amount of Time

Solution: You can use VBA to set up a timer with Application.OnTime that automatically closes the workbook after a specified duration. This is an effective way to enforce trial limits.

Combine this with XLS Padlock’s security options to forbid access to the VBA editor and compile your VBA code to prevent users from disabling the timer.

Implementation

Section titled “Implementation”

Insert the following code into the ThisWorkbook module of your VBA project.

' --- In ThisWorkbook module ---


' Variable to store the scheduled time for the timer
Private mScheduledTime As Date


' This procedure will be called by the timer to close the workbook
Public Sub CloseAndSave()
    ' Save any changes and close the workbook
    ThisWorkbook.Close SaveChanges:=True
End Sub


' This event runs when the workbook is opened, starting the timer
Private Sub Workbook_Open()
    ' Set the timer to run the "CloseAndSave" procedure in 8 hours.
    ' You can change the time value as needed.
    mScheduledTime = Now + TimeValue("08:00:00")
    Application.OnTime EarliestTime:=mScheduledTime, Procedure:="ThisWorkbook.CloseAndSave"


    ' Optional: Inform the user that the application will close automatically.
    ' MsgBox "This application will automatically close in 8 hours.", vbInformation
End Sub


' This event runs just before the workbook closes
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Cancel the scheduled OnTime event to prevent errors if the user
    ' closes the workbook manually before the timer runs.
    On Error Resume Next
    Application.OnTime EarliestTime:=mScheduledTime, Procedure:="ThisWorkbook.CloseAndSave", Schedule:=False
End Sub

How It Works

Section titled “How It Works”
  1. Workbook_Open: When the application starts, this event schedules the CloseAndSave macro to run after 8 hours (TimeValue("08:00:00")). You can adjust this duration to fit your needs (e.g., TimeValue("01:00:00") for one hour).
  2. CloseAndSave: This is the macro that performs the action. It saves the workbook and then closes it.
  3. Workbook_BeforeClose: This is a crucial cleanup step. If the user closes the workbook manually, this code cancels the pending timer, preventing Excel from trying to run a macro on a workbook that is no longer open, which would cause an error.