Excel application focus

[rowett]

Hi,
Does anyone know how to get an event when Excel itself becomes the focus or loses focus?
Excel appears to have events at the workbook and sheet level (i.e. when a workbook loses or gains focus, and the same for a sheet) but I can't find anything to let me know that Excel itself has lost focus.

Many thanks,
Chris

 

[Legacy 98055]

You could create your own event but I do not see the need? If you trap it with WindowActivate that should suffice. If a workbook is loaded and Excel gets focus, then so does the workbook. What are you trying to do? Overall?

Tom

 

[rowett]

Hi,
I've got a spreadsheet that has some VBA which times how long you are on a particular sheet. I want to pause the timer when you either a) switch to another sheet, b) switch to another workbook or c) switch to another application (like word, outlook etc). I then d) want the timer resumed when you switch back to the sheet in question.

a) and b) you can achieve with Sheet_Deactivate and Workbook_Deactivate events. d) can be achieved with Sheet_Activate and Workbook_Activate events. However no events go off (including Window_Activate) when you switch to another application (like Word) and then switch back to Excel.

Chris

 

[Legacy 98055]

I see... To go beyond the app's events, use a system callback. DO NOT use stop in your VBAIDE. Excel will close without saving changes. There is only one correct way to stop the API timer. Call StopTimer in your workbook_close or call it from somewhere else. This example shows the amount of time "Sheet1" is active in range A1 to the full second. You can tweak it to hundedths if you like. Format to show the time value. Ex. hh:mm:ss Code goes in a standard module. Sub RunTimer must go in a standard module for the pointer.

Option Explicit

Private Declare Function GetForegroundWindow Lib "user32" () As Long

Private Declare Function SetTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Private lngTimerID As Long
Private strTrackSheet As String
Private lngXlMainHwnd As Long
Private dblTotalTime As Double

Public Sub StartTimer(strSheetName As String)
    strTrackSheet = strSheetName
    lngXlMainHwnd = FindWindow("XLMAIN", vbNullString)
    dblTotalTime = 0
    lngTimerID = SetTimer(0, 1, 1000, AddressOf RunTimer)
End Sub

Public Sub StopTimer()
    Dim lRet As Long
    lRet = KillTimer(0, lngTimerID)
End Sub

Private Sub RunTimer(ByVal hwnd As Long, _
    ByVal uint1 As Long, _
    ByVal nEventId As Long, _
    ByVal dwParam As Long)
    On Error Resume Next
    Dim lngFrontWin As Long
    
    lngFrontWin = GetForegroundWindow()
   
    If ActiveSheet.Name = strTrackSheet _
    And lngXlMainHwnd = lngFrontWin Then
        dblTotalTime = dblTotalTime + 0.0000115741
        Range("A1").Value = dblTotalTime
    End If
    
    
End Sub

Public Sub Example()
    Call StartTimer("Sheet1")
End Sub

Tom