Excel

Application window activate, deactivate, and resize events

In this tutorial, you’ll learn how to capture WindowActivate, WindowDeactivate and WindowResize application-events. These events affect all opened windows 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

Note: After writing the procedures, you must save, close and reopen the workbook or the application-events won’t be captured.

WindowActivate

Syntax: App_WindowActivate(Wb, Wn)

The WindowActivate event occurs when you activate (focus on) any workbook window. The App_WindowActivate procedure is used to control the Excel window appearance for different workbooks. The App_WindowActivate event has two arguments: Wb as Workbook represents the workbook and Wn as Window represents the current Excel window.

App_WindowActivate Example:

This example maximizes any workbook window when its get activated:

Option Explicit

Public WithEvents App As Application

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
 Wn.WindowState = xlMaximized
 MsgBox Wb.Name & " activated and maximized"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

If you want to capture this event for a particular workbook, see Workbook_WindowActivate.

WindowDeactivate

Syntax: App_WindowDeactivate(Wb, Wn)

The WindowDeactivate event occurs when you deactivate (shift the focus away) any workbook window. The App_WindowDeactivate event has two arguments: Wb as Workbook represents the workbook and Wn as Window represents the deactivated Excel window.

App_WindowDeactivate Example:

This example displays a message box when you shift the focus away from any workbook window:

Option Explicit

Public WithEvents App As Application

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
 MsgBox Wb.Name & " widow deactivated"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

If you want to capture this event for a particular workbook, see Workbook_WindowDeactivate.

WindowResize

Syntax: App_WindowResize(Wb, Wn)

The WindowResize event occurs when you minimize, maximize, or resize any workbook window. The App_WindowResize procedure has two arguments: Wb as Workbook represents the workbook and Wn as Window represents the resized Excel window.

App_WindowResiz Example:

This example writes the “WorkbookName has been resized” on the status bar when you resize, minimize or maximize any workbook:

Option Explicit

Public WithEvents App As Application

Private Sub App_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
 Application.StatusBar = Wb.Name & " has been resized"
End Sub

Private Sub Workbook_Open()
 Set App = Application
End Sub

If you want to capture this event for a particular workbook, see Workbook_WindowResize.