How to use Application.OnWindow in the xlwings API way

In Excel’s object model, the Application.OnWindow property is a very useful member that allows developers to assign a macro or a procedure to run whenever any workbook window is activated (i.e., brought to the front) within the Excel application. This enables automation of tasks that should respond to window switching, such as updating a dashboard, refreshing data, or adjusting UI elements based on the active workbook.

In xlwings, which provides a Pythonic way to interact with Excel via its COM API, you can access this property through the Application object. The OnWindow property is a read/write string that accepts the name of a macro (as stored in Excel) to be executed. It is important to note that the assigned macro must be available in a currently open workbook, typically within a standard module.

Syntax and Parameters:
In xlwings, you can set or get the OnWindow property using the following approach:

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active

# Set the OnWindow property to a macro name
app.api.OnWindow = "MacroName"

# Get the current OnWindow property value
current_macro = app.api.OnWindow

• app.api.OnWindow: This accesses the underlying COM Application.OnWindow property. The api attribute in xlwings provides direct access to the raw Excel object model.
• Value: The property expects a string that is the name of a macro (e.g., "MyWindowHandler"). To clear the assignment, set it to an empty string ("").

Key Points:

• The macro specified must be written in VBA and reside in a module of an open workbook. It cannot be a Python function directly; xlwings can bridge this by calling Python from VBA, but the OnWindow property itself only accepts VBA macro names.
• The event triggers whenever any workbook window is activated, including switching between different windows of the same workbook.
• This property is application-wide, meaning it affects all workbooks open in that Excel instance.

Example Usage:
Suppose you have a VBA macro named UpdateStatusBar in a workbook that updates the status bar with the active window’s name. You can assign it via xlwings as follows:

import xlwings as xw

# Start or connect to Excel
app = xw.App(visible=True)

# Open a workbook containing the macro (e.g., 'Book1.xlsm')
wb = app.books.open('Book1.xlsm')

# Set the OnWindow property to trigger the macro
app.api.OnWindow = "UpdateStatusBar"

# Now, whenever you switch windows, the macro will run
# For demonstration, activate another window
app.books.open('Book2.xlsx').activate()

# To check the current assignment
print(f"OnWindow macro is set to: {app.api.OnWindow}")

# To remove the assignment
app.api.OnWindow = ""

# Close the workbooks and quit
wb.close()
app.quit()