• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2651
  • Last Modified:

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.
0
AL_XResearch
Asked:
AL_XResearch
  • 11
  • 9
3 Solutions
 
AL_XResearchAuthor Commented:
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
 
AL_XResearchAuthor Commented:
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
 
AL_XResearchAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
gowflowCommented:
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
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
You mentioned Addins. You developed them or ... ?
gowflow
0
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:

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
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
Sorry but do not know this language cannot assist you with  this language.
VBA in Excel fine but not this 'chineese'

:)
gowflow
0
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
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
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
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
 
byundtCommented:
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
 
AL_XResearchAuthor Commented:
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
 
gowflowCommented:
ok so your issue is solved then
gowflow
0
 
AL_XResearchAuthor Commented:
I resolved the issue on my own using my own techniques but gowflow provided some useful ideas and points-of-view
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now