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?

 

[Logit]

Is the workbook already open when you return from Chrome ?

 

[brianal2000]

Yes, it is already open. The use case is:

1) Screen 1 has an Excel spreadsheet open.
2) I need go to activate another app on Screen 2.
3) When I go back to the spreadsheet (re-activate it), I need to run a macro automatically. This action doesn't trigger Workbook_Activate() or Worksheet_Activate().

 

[Logit]

Not certain how this will work. Try it and let me know :

' In ThisWorkbook module:
Private Sub Workbook_Open()
    ' Enable the event by setting the Application-level variable
    Application.EnableEvents = True
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    ' This event triggers when the workbook window is activated
    Call RunOnActivate
End Sub

' In a standard module:
Public Sub RunOnActivate()
    ' Your macro code goes here
    MsgBox "Welcome back to Excel! Running your macro...", vbInformation
    ' Add your desired actions here
End Sub

 

[brianal2000]

Window_Activate() is only triggered when moving between workbooks, and NOT triggered when coming from another app...same as Workbook_Activate() and Worksheet_Activate().

 

[brianal2000]

Looks like I found it!

Workbook_SheetSelectionChange() is being triggered for this use case!

Thank you for your help!

Al

 

[brianal2000]

Looks like I spoke too soon. It isn't actually working as needed.
And I need to clarify. I'm using Alt-Tab to activate the Excel sheet (not select a cell), which doesn't trigger anything.
(I was selecting a different cell previously, which was triggering Workbook_SheetSelectionChange()).

 

[Logit]

How about this ? Untested here. Let me know if it works.

Declare the necessary API functions in a standard module:

' In a standard module:
Declare Function GetForegroundWindow Lib "user32" () As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Public Sub RunOnActivate()
    ' Your macro code goes here
    MsgBox "Welcome back to Excel! Running your macro...", vbInformation
    ' Add your desired actions here
End Sub

Private Sub CheckAppActivate()
    Dim hwnd As Long
    Dim title As String * 255
    Dim length As Long
    
    hwnd = GetForegroundWindow()
    length = GetWindowText(hwnd, title, Len(title))
    
    If length > 0 Then
        title = Left$(title, length)
        If InStr(title, "Microsoft Excel") > 0 Then
            Call RunOnActivate
        End If
    End If
End Sub

In the ThisWorkbook module :

' In ThisWorkbook module:
Private Sub Workbook_Open()
    ' Enable the event by setting the Application-level variable
    Application.OnTime Now + TimeValue("00:00:01"), "CheckAppActivate"
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    ' This event triggers when the workbook window is activated
    Application.OnTime Now + TimeValue("00:00:01"), "CheckAppActivate"
End Sub

By using this approach, the CheckAppActivate subroutine will be called every second to check if Excel is the active application, and if it is, it will call the RunOnActivate subroutine.

 

[brianal2000]

Sorry, but Workbook_WindowActivate() is just like the other events; only getting called when moving between workbooks, and not when moving from Alt-Tabing from Chrome to the workbook. Your code is working however when switching between worksheets.

Nice WinAPI calls! For others interested, add "PtrSafe" to the Declarations if you're using 64-bit Excel:

Declare PtrSafe Function...

Thanks for your persistent help!

 

[Logit]

Believe this works as desired ...

In a Regular module :

Declare Function GetForegroundWindow Lib "user32" () As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Public ActiveCheck As Boolean
Public TimerActive As Boolean

Public Sub RunOnActivate()
    ' Your macro code goes here
    MsgBox "Welcome back to Excel! Running your macro...", vbInformation
    ' Add your desired actions here
End Sub

Public Sub CheckAppActivate()
    Dim hwnd As Long
    Dim title As String * 255
    Dim length As Long
    
    hwnd = GetForegroundWindow()
    length = GetWindowText(hwnd, title, Len(title))
    
    If length > 0 Then
        title = Left$(title, length)
        If InStr(title, "Microsoft Excel") > 0 Then
            If Not ActiveCheck Then
                ActiveCheck = True
                Call RunOnActivate
            End If
        Else
            ActiveCheck = False
        End If
    End If
    
    ' Schedule the next check if the timer is active
    If TimerActive Then
        Application.OnTime Now + TimeValue("00:00:01"), "CheckAppActivate"
    End If
End Sub

Public Sub StartTimer()
    TimerActive = True
    CheckAppActivate
End Sub

Public Sub StopTimer()
    TimerActive = False
End Sub

In the ThisWorkbook module :

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

The macro checks every second to see if you have returned to the workbook.