Creating Application Activate Event

[duGly]

I am using VBA with Excel 2010. I am familiar with creating event handling procedures at the worksheet and workbook levels (as well as smaller objects such as UserForm or various controls). I want to capture an Activate event when the user switches to another application, and then comes back (activates) Excel. The worksheet activate event does not fire, because I'm not changing the active sheet (nor do I want to). Nor does the workbook activate event, because I'm not changing active workbooks.

I have created a class module to try capture the event. It works when I first open Excel, but then fails to run when I leave and come back to Excel. Here is the code:

' ••cls_mdlExcelEvents Class Module••
Private WithEvents App As Application
Option Explicit

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox "It Works!", vbOKOnly, "Test"
End Sub

Here is the reference to my class module from my ThisWorkbook module:

' ••ThisWorkbook Module••
Private xlApp As cls_mdlExcelEvents
Option Explicit

' •Workbook_Open Event Handler•
Private Sub Workbook_Open()
    Set xlApp = New cls_mdlExcelEvents
End Sub

[Leith Ross]

Hello duGly,

Activation has different meanings depending on the context. In VBA the application activate event is fired when the application is first loaded and started. The events you are trying capture are LostFocus and GotFocus. When the window that is receiving user input switches, the Windows message "WM_ACTIVATE" is sent to both windows. Messages are handled by the WndProc (Window Procedure) for each window. The low order word of the message is checked by each WndProc. If it is a 0 (zero) then that window is deactivated (no longer receives input). If it is a 1 or 2 then the window receiving the message will be activated (Receive input and be brought to the top of all other windows).

Unfortunately, the events are not exposed through the VBA interface. Using the Windows API, you can intercept and process messages sent or posted to a particular window before the window has a chance to process them. This technique is known as Subclassing. By Subclassing a window, an application can augment, modify, or monitor the behavior of the window. An application subclasses a window by replacing the address of the window's original window procedure with the address of a new window procedure, called the subclass procedure. Thereafter, the subclass procedure receives any messages sent or posted to the window.