Schedule to close Workbook using Application.OnTime

.

Dear Friends,

In this article I am going to teach you about Application.OnTime. I will also explain you the usage of this method with two examples:

How to schedule a workbook to close at specified time
How to Schedule a workbook to close if left idle for specified duration

This method is used to execute a procedure in Excel VBA at a specific time or after certain time has elapsed.
Lets have a look at the syntax of this Excel VBA method:

Syntax:

Application.OnTime( EarliestTime , Procedure , LatestTime , Schedule )

Where:

EarliestTime: This is a mandatory parameter and this is the time when you want to run your procedure. It accepts a variant data type.
Procedure: This is also a mandatory parameter. It is the name of the procedure/macro you want to run. It is a string type parameter.
LatestTime : This is an optional parameter. This is a maximum waiting time set by programmer for Excel to complete any ongoing activity like Cut, Copy, Paste, Save etc. and then start this procedure. Note that, if other ongoing activities are not complete within the specified waiting time then the procedure will not run. Since this is an optional parameter, if omitted, Application.OnTime method will wait till excel does not finish all its activities.
Schedule : This is an optional parameter. This is a boolean type parameter. True is used to schedule new procedure. False is used to clear out previously scheduled procedure.

Example 1# Schedule to save and close workbook

Follow the below steps to achieve this:

Step 1#

Copy and paste the following code in your ThisWorkbook Code window


Private Sub Workbook_Open()
    On Error Resume Next
    ' Schedule the Save&CloseWorkbook procedure at 5:30 PM
    Application.OnTime VBA.TimeValue("17:30:00"), "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    'Before closing the workbook, remove the schedule
    Application.OnTime VBA.TimeValue("17:30:00"), "SaveAndCloseWorkBook", , False
    On Error GoTo 0
End Sub

Step 2#

Last but not the least step, copy paste the Save and Close workbook function which will be triggered at scheduled time in any of the regular module in your VBE project.


Sub SaveAndCloseWorkBook()
    ThisWorkbook.Close SaveChanges:=True
End Sub

Example 2# Save and Close workbook, if left idle

Before we proceed further, let see – How to check if excel was left idle?
Here I am checking following two events to determine if excel was left idle-
SelectionChange in Workbook Event
Any Change across workbook Event

If any of the above events are happening with workbook, idle duration will be reset back to zero again.

What is use of this?

This is very useful when a workbook is shared to be used by more than one user. Incase any user left the excel open after working then, it will not be availbale for other users until he himself logs in and close the excel.
In such case, this little macro will help. It will automatically save and close the workbook if it was left idle for certain time (you can configure whatever you wish to)

Step 1#

Copy and paste the following code in any module in your VBE Project. Read basic tutorials – how to add modules in VBE.
Note that the following 2 lines must be added at the top of the module. It should not be pasted after any function/procedure in the module.


Public runTime As Double
Public Const DURATION_IN_HOURS = 0
Public Const DURATION_IN_MINUTES = 30
Public Const DURATION_IN_SECONDS = 0

You can use this duration according to your need. In this function I have used it for 30 minutes.

Step 2#

Copy and paste the following code in your ThisWorkbook Code Window.


Private Sub Workbook_Open()
    On Error Resume Next
    ' Set the runTime public varibale with the specified duration
    ' To change the time you can change in the public Constants
    ' defined in one of your module
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    ' Now schedule the Save&CloseWorkbook procedure with the specified duration
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next
    ' As explained above to reset the scheduled time for this save and close procedure
    ' Scheduled - False parameter is used
    Application.OnTime runTime, "SaveAndCloseWorkBook", , False
    
    ' Now reset the runTime variable again based on the new time
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    'Now re-schedule the Save&Close function with the new calculated time.
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
    ' As explained above to reset the scheduled time for this save and close procedure
    ' Scheduled - False parameter is used
    Application.OnTime runTime, "SaveAndCloseWorkBook", , False
    
    ' Now reset the runTime variable again based on the new time
    runTime = Now + TimeSerial(DURATION_IN_HOURS, DURATION_IN_MINUTES, DURATION_IN_SECONDS)
    
    'Now re-schedule the Save&Close function with the new calculated time.
    Application.OnTime runTime, "SaveAndCloseWorkBook", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    
    'Before closing the workbook, remove the schedule
    Application.OnTime RunWhen, "SaveAndCloseWorkBook", , False
    On Error GoTo 0
End Sub

Step 3#

Last but not the least step – write your own procedure which you want to schedule it. Make sure that the name of the procedure exactly same as you have provided in the Application.OnTime method in above code.

Now Paste this SaveAndCloseWorkbook function code any where in any of the regular Modules (not in worksheet or workbook module)


Sub SaveAndCloseWorkBook()
    ThisWorkbook.Close SaveChanges:=True
End Sub

Important:

Here I have provided only two basic usage of this function. You can use it even for triggering any of your procedure to run at specific interval etc.
If you find any other interesting usage of this method, do not forget to share it with every one by commenting to this article.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest