Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

Excel VBA detect change to non-Excel window

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
 
End Sub
0
crm-serv
Asked:
crm-serv
  • 2
1 Solution
 
regmigrantCommented:
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
0
 
crm-servAuthor Commented:
Thank you.  I'll take a look.
0
 
crm-servAuthor Commented:
I was hopeful of a more direct approach but realize now that is not available.

I appreciate you pointing me in the right direction and your prompt response.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now