UserForm_Activate event issue when using mouse hook

[cristianbuse]

User hartacus on Stack Overflow comment to post

This works quite well and smoothly. However, I have noticed that when the VBA editor is open, UserForm's _Activate events don't trigger when the mouse is hooked and when focus shifts to an external application then back again. I need to manually run DisableMouseScroll for the active UserForm before buttons will function. Mouse scroll can be re-enabled and everything works again until next time focus shifts. Any ideas?

[hartacus]

Thanks for opening this from my comment. I have done some digging and I have found that commenting out the section in GetCallbackPtr marked as "Fake callback signature to force fix stack parameters" seems to stop this behaviour. Still testing so not 100% sure that's it, nor why.

EDIT: actually that was a red herring, and I'm back to square one. What I have found, though, is that after running EnableMouseScroll on a userform, then switching to the VBA editor, the VBA editor keeps focus even when clicking the userform. I didn't notice this before because I had the VBA editor and the userform on different screens, but with them on the same screen I can see the VBA editor shifting to foreground after every click on the userform or the main Excel window. I can break this behaviour by clicking on the Excel main window's preview from the taskbar, so Windows seems able to force a focus change. This doesn't seem to happen after running DisableMouseScroll, the VBA editor behaves like a normal window.

[cristianbuse]

Hi @hartacus ,

Apologies, I haven't looked into this, I was quite busy the last few days. Only last night I was able to commit something to solve another request raised privately by someone else.

Just had a quick play and I managed to replicate the issue.

Needless to say, it should not happen. I can see all events are hijacked e.g. clicking a button triggeres the callback but not the button event. Within the MouseProc procedure, the last line CallNextHookEx is supposed to pass the mouse data to the next hook in the call chain. I will investigate this and let you know.

[cristianbuse]

Hi @hartacus ,

This was a tricky one. Please let me know if the latest commit has solved the issue. Many thanks for your feedback!

[hartacus]

Thanks @cristianbuse, that has gone a long way towards it. I wasn't comfortable with how it would switch focus away from the VBE, as it made it difficult to use the VBE while the userform was loaded. I have tried making a few small modifications to suit my use case, including shifting the check of foreground window to near the start of ProcessMouseData (instead of after ProcessDisplay) and make it exit the sub if the foreground window caption matches VBELabel, but ultimately still working on it.

[cristianbuse]

Hi @hartacus ,

make it exit the sub if the foreground window caption matches VBELabel

I initially did just that, but I did not like it as it would greatly impact debugging/stepping through code line by line.

I tried many ways of switching the foreground window but none of them worked if called before the DoEvents line. That is precisely why the call is now after the Do Events.

On the DoEvents line, the VBE regains the focus even if it was switched before. I did not look into this further as it all looked fine in my testing. Is there any reason why the current approach is not working for you?

[hartacus]

Thanks for looking into it. When I'm using the VBE, I can scroll the VBE window but opening the drop-downs for selecting subs results in them immediately closing again, and it's not possible to open forms or code from the Project Explorer as it just opens the active form in the editor instead. If that doesn't happen for you, it could be a part of this project that is unexpectedly interfering, it's a behemoth that I inherited in unmaintained state.

[cristianbuse]

When I'm using the VBE, I can scroll the VBE window but opening the drop-downs for selecting subs results in them immediately closing again, and it's not possible to open forms or code from the Project Explorer as it just opens the active form in the editor instead. If that doesn't happen for you

Yes, it does happen for me. I just spent a few hours on this and once the VBE takes focus I can't find a way to set the focus back to the form or even the app window itself. So, it's either the form continues to work fine or the VBE dropdowns work fine. Unfortunately not both at the same time.

[cristianbuse]

Will look into this another time and will keep the issue opened until then. As mentioned above, once VBE takes focus then the problem begins. However, I believe the issue could happen because of the Async call via the class Terminate event. I will need to play around with other async aproaches e.g. PostMessage but only in a few days time.

[cristianbuse]

Hi @hartacus ,

I think the last commit finally solves this issue. Please let me know if it works for you. Thanks!

[hartacus]

Hi @cristianbuse , the last commit is working very well now. The VBE takes focus correctly and scrolling appears to work properly throughout all of the windows I have implemented it in my project. Thanks very much!

[cristianbuse]

Hi @hartacus ,

Great. Thanks for the feedback!