Excel

Application AfterCalculate, WorkbookActivate & WorkbookActivate Events

In this tutorial, you’ll learn how to run procedures automatically by using the application-level events WorkbookActivate and WorkbookDeactivate when you activated or deactivated any workbook in an Excel session. You’ll also learn about the AfterCalculate event which occurs when all calculations have been completed in an Excel session. Let’s open VBE (press Alt+F11) and write the following code in ThisWorkbook code module (for details, visit how to capture application events: 

Option Explicit

Public WithEvents App As Application

Private Sub Workbook_Open()
 Set App = Application
End Sub

WorkbookActivate

Syntax: App_WorkbookActivate(Wb)

The WorkbookActivate event occurs when any workbook is activated. For example, when the user shifts the focus to an open workbook. The App_WorkbookActivate procedure has one argument Wb which represents the activated workbook.

App_WorkbookActivate Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
 MsgBox Wb.Name & " activated"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookActivate is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_Activate procedure.

WorkbookDeactivate

Syntax: App_WorkbookDeactivate(Wb)

The WorkbookDeactivate event occurs when any workbook loses focus. The App_WorkbookDeactivate procedure has one argument Wb which represents the deactivated workbook.

App_WorkbookDeactivate Example:

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
 MsgBox Wb.Name & " deactivated"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

The WorkbookDeactivate is an application-level event that affects all open workbooks in an Excel session. To work with a particular workbook use the Workbook_Deactivate procedure.

AfterCalculate

Syntax: App_AfterCalculate()

The AfterCalculate event occurs when all refresh and calculation activities have been completed, and no outstanding queries exist.

App_AfterCalculate Example:

Option Explicit

Public WithEvents App As Application

Private Sub App_AfterCalculate()
 MsgBox "All calculations have been completed on all workbooks"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

This event occurs for all opened workbooks, use Workbook_SheetCalculate for a particular workbook or Worksheet_Calculate for a particular worksheet.