?
Solved

Excel VBA - detecting when an Excel instance loses focus

Posted on 2014-02-04
21
Medium Priority
?
2,167 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
[X]
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
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 31

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 31

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 31

Assisted Solution

by:gowflow
gowflow earned 900 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 31

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
 
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 31

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 31

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 31

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 31

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 81

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 31

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

764 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