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
Next
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"
Wn.Activate
End If
End If
You would need to have a hook into the Windows level event handler (ie: outside excel). I believe this is only possible with a DLL to handle it for you however the following link points to a possible open source solution which is intended for VB but may provide a way forward for you:-
http://www.vbaccelerator.com/home/index.asp
http://www.vbaccelerator.com/home/VB/Code/Libraries/Hooks/vbAccelerator_Hook_Library/article.asp