Is there a way to use Excel VBA to detect when the user changes to another application window ( not an Excel window )?
The Excel app may or may not have a userform active at the time of the switch.
I have tried the workbook_deactivate event and found that it seems to fire only for a change to another workbook but not to another application.
This is code I have tried -
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Dim f, fc, fl, fs, count, NewFilePath
'check for existence of file in folder LatestTestfile
Set fs = CreateObject("Scripting.FileSystemObject")
NewFilePath = ThisWorkbook.Path & "\LatestTestFile"
Set f = fs.GetFolder(NewFilePath)
Set fc = f.Files
count = 0
For Each f1 In fc
count = count + 1
If (count > 0) Then
If (InStr(1, ThisWorkbook.Name, "console") > 0) Then
MsgBox "A test has not been logged. Complete the log entry before leaving the console window"