Excel

Window Events for Workbook

In this tutorial you’ll learn about the WindowActivate, WindowDeactivate and WindowResize events. These event occurs when you switch between the workbooks or resize (such as, maximizes or minimizes) a workbook window. Press Alt + F11 to open the Visual Basic Editor (VBE), from the Project Explorer double-click the ThisWorkbook object, as shown in the figure, to write the event-handler procedures for these events:

Windows Events VBA

Workbook_WindowActivate

Syntax: Workbook_WindowActivate(Wn)

The WindowActivate event occurs when a user activates (focus on) the workbook window. To listen this event, use the Workbook_WindowActivate event listener procedure to control the Excel window appearance for different workbooks.

The Workbook_WindowActivate event procedure has one Wn as Window argument which represents the current workbook window. The example below, maximizes the workbook window when the user activates the workbook containing the code of the Workbook_ WindowActivate procedure:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
 Wn.WindowState = xlMaximized
End Sub

Note: This event occurs only if you are activating the workbook window by switching from the another workbook window.

Workbook_WindowDeactivate

Syntax: Workbook_WindowDeactivate(Wn)

The WindowDeactivate event occurs when a user deactivates (shift the focus away) the workbook window. To listen this event, use the Workbook_WindowDeactivate event listener procedure to control the Excel window appearance for different workbooks.

The example below sends the close command to the workbook window when the user deactivates the workbook containing the code of the Workbook_ WindowDeactivate procedure. If you’ve not already saved the changes then Excel asks you to save the changes:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
 MsgBox Wn.Close()
End Sub

Note: You need to open at least two workbooks to trigger this event. This event will only occurs if you switch between the workbooks. This event not occurs when you shifts the focus away from any window other than the workbook.

Workbook_WindowResize

Syntax: Workbook_WindowResize(Wn)

The WindowResize event occurs when a user minimizes, maximizes or resizes the workbook window. To listen this event, use the Workbook_WindowResize event listener procedure to control the Excel window appearance for the workbook.

The example procedure writes the “WorkbookName has been resized” on the status bar when the user resize, minimize or maximize the workbook containing the code of the Workbook_ WindowResize procedure:

Excel StatusBar showing window resize message

Workbook_WindowResize Example: 

Private Sub Workbook_WindowResize(ByVal Wn As Window)
 Application.StatusBar = Wn.Caption & " has been resized"
End Sub