Excel VBA - detecting when an Excel instance loses focus
Posted on 2014-02-04
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.