asked on

Excel VBA - detecting when an Excel instance loses focus

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.

AL_XResearch

ASKER

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.

AL_XResearch

ASKER

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 ?

AL_XResearch

ASKER

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'

Jacques Geday

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

AL_XResearch

ASKER

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.

Jacques Geday

You mentioned Addins. You developed them or ... ?
gowflow

AL_XResearch

ASKER

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.

SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AL_XResearch

ASKER

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

Jacques Geday

Sorry but do not know this language cannot assist you with this language.
VBA in Excel fine but not this 'chineese'

:)
gowflow

AL_XResearch

ASKER

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.

Jacques Geday

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

ASKER CERTIFIED SOLUTION

AL_XResearch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacques Geday

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

Jacques Geday

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

SOLUTION

AL_XResearch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jacques Geday

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

byundt

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.

AL_XResearch

ASKER

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

Jacques Geday

ok so your issue is solved then
gowflow

AL_XResearch

ASKER

I resolved the issue on my own using my own techniques but gowflow provided some useful ideas and points-of-view