Solved

Excel VBA - detecting when an Excel instance loses focus

Posted on 2014-02-04
21
1,832 Views
Last Modified: 2014-02-10
I have a sophisticated Excel 2010 development which is implemented as an Addin (XLAM). This addin contains all the code and an 'event watcher' class to intercept Excel's 'Application' events.  The system opens a number of child workbooks to allow the user to enter and review key data.

Part of the code's function is to detect when child workbooks are opened / close and apply / rescind restrictions such as disabling copy and paste and reassigning function keys. This all works fine.

My problem is when you start a new session of Excel (i.e. a new application session NOT a new workbook). Due to the fact there are no in-built VBA events to detect when an Excel session loses focus I am unable to re-enable items such as copy & paste. This means that even after my system is closed the copy & paste are disabled for the rest of the windows session. Unacceptable.

Can anyone suggest a way to have Windows or Excel notify me when the user switches from one Excel instance to another or closes my instance only ?

I have seen the Windows API event 'SetWinEventHook' but from my further reading this looks like it could be very problematic and crash Excel  if you are not very very careful to stop the hook.

Any suggestions or examples would be extremely appreciated.
0
Comment
Question by:AL_XResearch
  • 11
  • 9
21 Comments
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832217
To approach my issue from another direction; The problem is that the state (enabled or disabled) or the commandbars / controls are preserved accross Excel sessions.

The same is true for the display of the 'formula bar'

This only happens on some machines though. This is even though I have not issued any command which would preserve the state in a new Excel session.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832236
Even if I close the additional 'test' session, re-enable the commandbars in my development and then open a new 'test' session the commandbars are still disabled ?

NOTE: I am talking about the commandbars in the 'Cell' menu

Is there a way of disabling the save of the commandbars' disabled status across sessions ?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832280
It appears my issue is, in part, because when I close my development in a certain way I am unable to reset the commandbars back to enabled - and any subsequent sessions pick up on the 'saved' setting of 'disabled'
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832285
try put this in the
Workbook_BeforeClose event

With Application

    .EnableEvents = True
    .CommandBars.ActiveMenuBar.Enabled = True
    .DisplayAlerts = True
    .DisplayFormulaBar = True
    .DisplayInsertOptions = True
    .DisplayPasteOptions = True
    .DisplayScrollBars = True
    .DisplayStatusBar = True
    .ScreenUpdating = True

End With

Open in new window


so that when your application is closing it is making sure it is enabling most of critical events.
gowflow
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832295
Thanks 'gowflow' but I am aware of this. As I posted recently the problem is that I can't set the commandbars back to being enabled before the Excel app closes - and the Excel app remembers that 'enabled' status of the commandbars for the subsequent session.

That is what I want to know if I can disable or if it is hard-coded into Excel.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832303
You mentioned Addins. You developed them or ... ?
gowflow
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832311
I developed it. A large addin (70 modules, 200 functions, 160 subs ....)

What I need is a way to ether stop Excel remembering the commandbar state or a way to intercept the close of Excel via the 'x' in the top-right. Due to the fact the controlling code is an addin the 'workbook_beforeclose' will not help.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 300 total points
ID: 39832323

Due to the fact the controlling code is an addin

Well That is EXACTLY where you need to put the block I gave you, and specifically in the  the 'workbook_beforeclose' OF THE ADDIN. Save recompile and replace existing ADDIN !!! :)
gowflow
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832357
I did think i had cracked it by disabling the 'x' via the my ribbon definition (see below) using the "FileExit" command but this appears to do nothing.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="rxc_OnLoadRibbon">
	<commands>
		<command idMso="Help" onAction="rxa_showQuickHelp" />
		<command idMso="WindowClose" onAction="rxca_closeCurrentModule" />
		<command idMso="WindowRestore" getEnabled="rxc_getWindowControlEnabled" />
		<command idMso="WindowMinimize" getEnabled="rxc_getWindowControlEnabled" />
		<command idMso="FileExit" getEnabled="rxc_getWindowControlEnabled" />
	</commands>

Open in new window


NOTE: 'rxc_getWindowControlEnabled' will always return FALSE
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832375
Sorry but do not know this language cannot assist you with  this language.
VBA in Excel fine but not this 'chineese'

:)
gowflow
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Comment

by:AL_XResearch
ID: 39832384
Sorry that is an extract of the RibbonX XML that defines my custom ribbon for the addin.

This 'Chinese' is the new commandbar definition for 2010 and onwards.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832402
I know. but do you have VBA in your original ADDIN workbook ? if yes then put what I gave you in the workbook_beforeclose event.

Maybe it will override the action of the change of ribbon done via XML all depend which one comes first. I read about the new ribbon way of controlling but frankly found it way too complex and do not have need to step in it so I never tried it reason why it is 'chineese' to me :)

gowflow
0
 
LVL 3

Accepted Solution

by:
AL_XResearch earned 0 total points
ID: 39832453
I agree that at first the RibbonX is a bit of a mind trip but after you get used to you will find it is actually quite logical and simple. All you need to make sure is you use a constant set of naming standards and approaches. It is not too different from commandbars except that you cannot query the state of the controls - you have to let them query your code.

Anyway, back to the my question;

I have fixed it now. I always had code in the BeforeClose event of the 'addins' workbook but it did nothing. It was the code's fault and not Excel (system was extended / adapted from an Excel 2003 system and the code was not updated). Basically I was thinking my event watcher class' 'BeforeClose' event would be hit first when closing the workbook and that is where I had placed the key code, my mistake.

I have simply set 'Cancel = true' so the big red 'x' does nothing. That forces the user to use the 'Exit system' button I have defined in the Backstage area. Since the only 'exit' is via a button I can have complete control about what fires and gets enabled / disabled.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832466
Do not agree with your reasoning !!!!

wether X or File Exit they both calls for Before_Close at some point.

Before lose is very tricky as if
inadvertently you pput and Exit sub somewhere before the end or
Application.Quit
or Set variable = nothing and that variable is the workbook itself then you are exiting this code before it terminates,

I have spent weeks and weeks troubleshooting issues with this section very difficult.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832480
What I suggest:

load your addin
put a halt at End Sub of Before Close (F9)
run the addin do all tricky things that you feel will disable your command bar
Close it with the X it should stop in Debug mode at the End Sub just before existing

Then
Try in the immediate window
?Application..CommandBars.ActiveMenuBar.Enabled
and all the other like
?Application.EnableEvents

and they should give you true if one give False then you incorporate this one into the code prior to End Sub

gowflow
0
 
LVL 3

Assisted Solution

by:AL_XResearch
AL_XResearch earned 0 total points
ID: 39832617
I know what you mean but I think by the use of a boolean to control when the cancel takes place (i.e. only when I intend it) I am safe from those issues.

My addin's workbook event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Cancel = Not cg_blnClientSystemClosing

End Sub

Open in new window


The code for the closing the system:

Sub closeClientWorkbook(Optional blnSlientClose As Boolean = True)

    Dim strMessage As String
    
    Dim varRetVal As Variant
    
    Dim wkbCurWorkbook As Workbook
    
    ' If not a silent close then confirm  with the user
    If Not blnSlientClose Then
    
        strMessage = "Are you sure you want to close the client workbook ?"
        
        varRetVal = MsgBox(strMessage, vbYesNo + vbQuestion, MARS_CLIENT_TITLE)
    
    End If
    
    If varRetVal = vbYes Or blnSlientClose Then
    
        ' Indicate the system is closing
        ' (so the addin's 'BeforeClose' will not prevent this action)
        cg_blnClientSystemClosing = True
        
        '  Perform additional code ...
        
        ' Remove Excel app restrictions
        restrictExcelInterfaceForWorkbook ThisWorkbook, False, False
        
        ' Close this session without prompting
        With Application
            .DisplayAlerts = False
            .Quit
        End With
        
    End If

End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
ID: 39832810
Here is probably your problem you are not entering this for some reason (sometimes)

If varRetVal = vbYes Or blnSlientClose Then
    
        ' Indicate the system is closing
        ' (so the addin's 'BeforeClose' will not prevent this action)
        cg_blnClientSystemClosing = True
        
        '  Perform additional code ...
        
        ' Remove Excel app restrictions
        restrictExcelInterfaceForWorkbook ThisWorkbook, False, False
        
        ' Close this session without prompting
        With Application
            .DisplayAlerts = False
            .Quit
        End With
        
    End If

Open in new window


Your  varRetVal = vbNo or your blnSlientClose is False
put a break there to make sure you always enter this routine as you need to make sure your code always execute this:

' Remove Excel app restrictions
restrictExcelInterfaceForWorkbook ThisWorkbook, False, False

that is my quick assumption but here again cannot confirm 100% as have nothing to test all this.
gowflow
0
 
LVL 80

Expert Comment

by:byundt
ID: 39834507
If your add-in checks for a Boolean key in the registry upon opening, it can determine whether another instance of Excel is already open. If so, it can let Excel use the normal ribbon. If not, the add-in will use the custom ribbon and set that Boolean.

Upon closing, the add-in will restore the Boolean. You will probably also want to give the user the ability to reset the Boolean.

The GetSetting function in VBA can retrieve the key, while the SaveSetting function will set it. Both functions are discussed in the VBA Help.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39835365
Hi 'byundt', thanks for the response. I am not sure I follow your logic here. I was not trying to test if another instance was open but determine when a specific Excel instance window (an 'OS Window' as opposed to a 'Workbook window') received focus.

Your method would mean that all other instances of Excel would need to check the registrry Boolean to determine what ribbon to use and hence each 'normal' Excel session would need to run a background addin to check this. This is not an option.

My overall goal was to apply / release workbook restrictions to a specific window but I have accomplished the same goal by preventing logout except by a button I have specified and therefore have complete control over.

---------------

[gowflow] My previous post (Click here) provides the solution and as I have said above circumvents the problem by stopping the user logging out via the window's red 'x' whilst allowing the VBA code to close the workbook
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39835374
ok so your issue is solved then
gowflow
0
 
LVL 3

Author Closing Comment

by:AL_XResearch
ID: 39846815
I resolved the issue on my own using my own techniques but gowflow provided some useful ideas and points-of-view
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

First some basics on Windows 7 Backup.  It has 2 components one is a file based backup which is stored in .zip files each zip is split at around 200 Megabytes and there is the Image Backup which is as the name implies a total image of the partition …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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