Triggering Workbook_Activate() When Coming From Another Application

[brianal2000]

The Workbook_Activate() event only triggers when moving from one workbook to another. It doesn't trigger however if you activate Excel when coming from another application (say, Chrome). How can I trap this action?

 

[Jaafar Tribak]

@Logit
Thanks .
I wouldn't personally use the OnTime Method @ 1 sec intervals as it would badly tie up the user interface. There are other *lighter* alternatives such as the CommandBars OnUpdate event which is not perfect but, will at least not tie up the application.
Also, GetWindowText won't necessarly return the string: "Microsoft Excel" as the application caption often varies. I would just use If GetForegroundWindow = Application.hwnd Then to check if excel is active.

Having said that, the propper way to achieve a pseudo Activate\DeActivate event for the main excel window is by using a ShellHook or by setting an Accessibilty event hook (SetWinEventHook) and monitoring EVENT_SYSTEM_FOREGROUND events.

 

[Logit]

Jaafar Tribak: :​

I am not familiar with the commands you speak of. Could you construct something for the OP so we can review it ?

 

[Jaafar Tribak]

Jaafar Tribak: :​

I am not familiar with the commands you speak of. Could you construct something for the OP so we can review it ?

The commandbars OnUpdate event can be hooked using the WithEvents Keyword. This event fires everytime the state of the commandbars changes. When the Excel application is Activated\DeActivated, the commandbars state changes so the OnUpdate event fires and we can take advantage of that to run our Activate pseudo-event that we are seeking here.

As for the SetWinEventHook, it is a user32 exported function that can be used to monitor\detect various Graphical User Interface elements including Activating\Deactivating windows. This method is more reliable than the above mentioned CommandBras OnUpdate hacky workaround but needs caution and proper error handling when used.

Here is a bare minimum code example that makes use of the CommandBras OnUpdate event for detecting when the excel application main window is being activated :

In the ThisWorkbook Module:

Option Explicit

Private WithEvents Cmbrs_Events As CommandBars

#If VBA7 Then
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As Long
    Private Declare PtrSafe Function GetAncestor Lib "user32" (ByVal hUf As Long, ByVal gaFlags As Long) As Long
    Private Declare PtrSafe Function AnyPopup Lib "user32" () As Long
#Else
    Private Declare Function GetActiveWindow Lib "user32" () As Long
    Private Declare Function GetAncestor Lib "user32" (ByVal hUf As Long, ByVal gaFlags As Long) As Long
    Private Declare Function AnyPopup Lib "user32" () As Long
#End If

Private Sub Workbook_Activate()
    Call MonitorActivateEvent(True)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     Call MonitorActivateEvent(True)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call MonitorActivateEvent(False)
End Sub

Private Sub MonitorActivateEvent(ByVal bMonitor As Boolean)
    If bMonitor Then
        If Cmbrs_Events Is Nothing Then
            Set Cmbrs_Events = Application.CommandBars
        End If
    Else
        Set Cmbrs_Events = Nothing
    End If
End Sub

Private Sub Cmbrs_Events_OnUpdate()
    Const GA_ROOT = 2&
    Static bNoPopUps As Boolean
    Static hPrevHnwd As Long
    On Error Resume Next
    If hPrevHnwd = 0& Then
        If GetAncestor(GetActiveWindow, GA_ROOT) = Application.hwnd And bNoPopUps Then
            Application.OnTime Now, Me.CodeName & ".Excel_Pseudo_Activate_Event"
        End If
    End If
    hPrevHnwd = GetActiveWindow: bNoPopUps = (AnyPopup = 0&)
End Sub

'           PSEUDO-EVENT
Private Sub Excel_Pseudo_Activate_Event()
    MsgBox "Excel Activated."
End Sub

 

[Logit]

Thank you for the instruction.

 

[brianal2000]

Yes, that does work!

In my case, I want to re-query something to show the most up-to-date- data on a page whenever user returns, instead of forcing them to press a button.
It seems like an oversight by Microsoft that the "Activate" events only run when moving from sheet to sheet, but don't when moving from a different application to a sheet.

Thanks again,
Al

 

[Logit]

You are welcome.