Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VBA detect change to non-Excel window

Posted on 2014-03-16
3
552 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
ID: 39932736
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
ID: 39932768
Thank you.  I'll take a look.
0
 

Author Closing Comment

by:crm-serv
ID: 39937141
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

860 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