Detect (in VBA) when a window containing an excel instance becomes active

Question

I can see how WindowActivate events fire at different levels when I switch between windows in excel, but is there a way to fire an event when excel becomes a foreground application? If I exit excel and work, for example, in the browser for a while, and then return to the excel window, I do not see any shelling of events. Is there any way to detect this?

I would like to update some elements of my VBA application because sometimes I find that my Mouse Over function, based on the Hypertext Function, loses the ability to activate diagrams. I can fix this without protecting and protecting the worksheet, or by iterating over and reinitializing a subset of my objects. I would like to trigger this action on the event I'm looking for.

I can also do this using SendKeys, but it is not very nice because it wipes the keyboard settings (e.g. scrolling lock) due to a documented error in SendKeys and this makes the screen flicker more than we would like.

Since the code will be in VBA, I would limit the action to a specific book. If another (passive) workbook is active when entering the Excel instance window, no action will be triggered, and I can use the WorkbookActivate event to update the application if and when the user selects a workbook containing it.

Answer 1

I believe this is not provided directly in Excel, so use the Windows API. You can do win32 programming in VBA!

Explanation

You can use the win32 api SetWinEventHook function to force Windows to inform you of some events. Including EVENT_SYSTEM_FOREGROUND, which is triggered when the foreground window changes. In the example below, I am checking the new process identifier of the foreground window for the process identifier of Excel. This is an easy way to do this, but it will detect other Excel windows, such as a VBA window, as well as the main Excel window. This may or may not be the behavior you want, and it can be changed accordingly.

You have to be careful with SetWinEventHook, since you are passing it a callback function. You are limited by what you can do in this callback function, it exists outside the normal VBA execution, and any errors inside it can cause Excel to crash in an unmanaged, unrecoverable way.

This is why I use Application.OnTime to report events. They fail if multiple events fire faster than Excel and VBA updates. But it is safer. You can also update the collection or array of events and then read them back separately from the WinEventFunc callback.

Code example

To verify this, create a new module and paste this code into it. Then run StartHook. Remember to run StopAllEventHooks before closing Excel or modifying the code! In the production code, you probably add StartEventHook and StopAllEventHooks to the WorkBook_Open and WorkBook_BeforeClose events to ensure that they will fire at the appropriate times. Remember, if something happens to the VBA WinEventFunc code before the interception is blocked, Excel will crash. . This includes a modifiable code or the book in which it is located is closed. Also, do not press the stop button in VBA while the hook is active. The stop button can erase the current state of the program!

Option Explicit Private Const EVENT_SYSTEM_FOREGROUND = &H3& Private Const WINEVENT_OUTOFCONTEXT = 0 Private Declare Function SetWinEventHook Lib "user32.dll" (ByVal eventMin As Long, ByVal eventMax As Long, _ ByVal hmodWinEventProc As Long, ByVal pfnWinEventProc As Long, ByVal idProcess As Long, _ ByVal idThread As Long, ByVal dwFlags As Long) As Long Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long Private pRunningHandles As Collection Public Function StartEventHook() As Long If pRunningHandles Is Nothing Then Set pRunningHandles = New Collection StartEventHook = SetWinEventHook(EVENT_SYSTEM_FOREGROUND, EVENT_SYSTEM_FOREGROUND, 0&, AddressOf WinEventFunc, 0, 0, WINEVENT_OUTOFCONTEXT) pRunningHandles.Add StartEventHook End Function Public Sub StopEventHook(lHook As Long) Dim LRet As Long If lHook = 0 Then Exit Sub LRet = UnhookWinEvent(lHook) End Sub Public Sub StartHook() StartEventHook End Sub Public Sub StopAllEventHooks() Dim vHook As Variant, lHook As Long For Each vHook In pRunningHandles lHook = vHook StopEventHook lHook Next vHook End Sub Public Function WinEventFunc(ByVal HookHandle As Long, ByVal LEvent As Long, _ ByVal hWnd As Long, ByVal idObject As Long, ByVal idChild As Long, _ ByVal idEventThread As Long, ByVal dwmsEventTime As Long) As Long 'This function is a callback passed to the win32 api 'We CANNOT throw an error or break. Bad things will happen. On Error Resume Next Dim thePID As Long If LEvent = EVENT_SYSTEM_FOREGROUND Then GetWindowThreadProcessId hWnd, thePID If thePID = GetCurrentProcessId Then Application.OnTime Now, "Event_GotFocus" Else Application.OnTime Now, "Event_LostFocus" End If End If On Error GoTo 0 End Function Public Sub Event_GotFocus() Sheet1.[A1] = "Got Focus" End Sub Public Sub Event_LostFocus() Sheet1.[A1] = "Nope" End Sub 

Answer 2

Here's how to do it using VBA: Detecting when Excel loses focus?