Solved

Excel VBA detect change to non-Excel window

Posted on 2014-03-16
3
513 Views
Last Modified: 2014-03-18
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
Comment
Question by:crm-serv
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
Comment Utility
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
 

Author Comment

by:crm-serv
Comment Utility
Thank you.  I'll take a look.
0
 

Author Closing Comment

by:crm-serv
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now