ActiveX Mouseover Limitations when using Multiple Windows

[jeremyjelley]

I need to use the mouseover functionality across multiple windows of the same workbook.

It works perfectly when only one window is open and intermittently works on multiple windows but that is rare.

Sometimes the initial window retains the mouseover ability and sometimes it is passed on to the new window.

I read in a forum a couple weeks ago (which I can't find any longer) that Access has the ability to have
mouseover functionality across all windows and but Excel does not (though it could intermittently work).
That forum mentioned what aspect of the application governs the multi window capabilities but I can't
remember or find it.

A solution that could work for my project is to swap the working mouseover window with the non-working
mouseover window. However that only works when the initial window retains mouseover functionality.
When it does not retain I don't know which window has it.

When the first window passes "it" on to a new window I need to be able to programatically test which
window it was passed to.

1) Do you know what what application resource governs mouseover with respect to the window it is located?

2) Is there a way to identify which window has mouseover control

3) Is there another possible solution I am missing?

Thank you for your help!

[xlnitwit]

Hi

What are you using the mouseover for? Could you use a userform?

Activex controls on worksheets are infamous for their unreliable behaviour at any time but exponentially more so if you use multiple windows on a workbook, going back at least as far as Excel 97 in my recollection.

[jeremyjelley]

I developed a back testing software for currency trading and have printed the results to excel for analysis. Each report is in its own window and contain tens of thousands to hundreds of thousands of permutations. Multiple reports are displayed at once for comparison. Each report has a key in a separate window showing stats of each permutation as the mouse moves over them. It is a very important function for this report.

The amount of data and the need for heat maps prohibits the use of Userforms for this project.

l have everything else working solidly so far. The objects themselves have been operating quite reliably. The final piece to the puzzle is to Identify which window has mouseover control so that I can switch to it when needed. Any guesses?

[xlnitwit]

Regrettably no- I can't think of any functions that would deliver that information. I assume that this is not information that might easily be displayed in a comment?

[jeremyjelley]

This information is definitely not provided with a standard excel function though it might easily be displayed in a comment if I knew what I was looking for.

I have been testing if it has something to do with parent/child windows but each window seems to be it's own parent.

I might need an API?? I'm don't know.

[xlnitwit]

What I meant was that I can't think of a function (VBA or API) that would tell you which window was receiving the mousemove messages for your activex control.

If you could put the stats into a comment, then they would automatically display on hovering over the cell, which would solve your problem, but I suspect the data you want to display would not easily convert into a comment?

[jeremyjelley]

Not so easily, no. The key is showing 3 things: About 30 stats, the permutation combinations are highlighted and there is an equity chart that updates for every permutation.

The key is also interactable. It has msforms objects that, when clicked, highlight different parts of the report.

[xlnitwit]

In that case, I can only really think of two options- subclassing the Excel window to trap the mouse messages directly without the activex control, or using a HYPERLINK formula with a UDF to run your code instead of the mousemove event. The former is prone to crashing Excel unless you are extremely careful (and it's a hard crash with no warning or error message) so the latter would be preferable if it is an option.

[jeremyjelley]

I initially went the HYPERLINK route. I can't remember why at the moment but that ended up not being usable.

I had starting checking out subclassing but couldn't find enough information and examples to make it work. It was very complicated and risky. If you know of some good resources I am open to exploring it some more.

[xlnitwit]

If you search over at MrExcel there is a poster by the name of Jaafar Tribak (I think that is the correct spelling) who does some truly incredible things with API functions. I think he has posted a few things about subclassing including, if I remember correctly, something involving an embedded dll which I have never had occasion to test but sounded promising.

I'd be intrigued to know what specifically caused you to abandon the HYPERLINK approach if you do remember. I know that not everything is possible directly from it but there are often workarounds for its limitations.

[jeremyjelley]

I am going back through my HYPERLINK code again right now to check out what happened there and I'll let you know.

I'll also head over to MrExcel and check out Jaafar. An embedded dll sounds interesting. Thank you!

[jeremyjelley]

Finally found what the issue was. For some reason when using the HYPERLINK method ScreenUpdating won't turn off. With the amount of data I am updating it takes way too long with out it.

Do you know of any workarounds for that?

[xlnitwit]

Have you tried the LockWindowUpdate API routine?

[jeremyjelley]

I've been trying to test LockWindowUpdate but I've been running into an issue. For some reason the HYPERLINKs have been randomly firing thousands of times when a cell value is changed. This is happening even when there is only window open, the sheet that contains the hyperlinks isn't active, the changed cell is in a different sheet and I manually change the cell without any VBA or events. The firing also starts on DoEvents when I run a module. I've not had this happen before and doesn't make any sense to me. I would show some code but the issue occurs without me running any code. Do you know what might be happening?

[xlnitwit]

Is your UDF volatile?

[jeremyjelley]

I don't have Application.Volitile anywhere in the project. Is it possible it was turned on another way? I am not in the office right now so I can't test anything at the moment.

[xlnitwit]

Only if you added a volatile function to the HYPERLINK call.

[jeremyjelley]

The HYPERLINK function wasn't volatile. At some point it stopped acting strangely and I was able to test it with the LockWindowUpdate API. It did speed it up a little but not enough. The ActiveX mouseover with setting screenupdating to false is still significantly faster.

I've been trying out Jaafar's mousemove subclassing example from here...
http://www.mrexcel.com/forum/excel-q...r-shape-2.html

I have the 2016 64 bit version of excel so I had to convert the api declarations to ptrsafe. Other than that everything else is the same. So far I've crashed excel 8 times. It crashes during the MessageLoop Procedure in the GetMessage Function.

I haven't been able to troubleshoot it past that point. Can you tell what is going wrong?

Here is my code...

ThisWorkbook Module:

Please Login or Register  to view this content.

Class Module named "CMouseMove"...

Please Login or Register  to view this content.

In a Standard Module:

Please Login or Register  to view this content.

[xlnitwit]

The wParam and lParam in the MSG type need to be LongPtr rather than Long. There may be more changes required as I have not had time for a full review of the code yet.

[xlnitwit]

I cannot currently test this but I believe your class should begin thus

Please Login or Register  to view this content.

and your callback proc should declare thus

Please Login or Register  to view this content.

which also requires a small change to GetHiloword

Please Login or Register  to view this content.

although truncating to a Long for lParam should be OK there.
and finally TransitionalProc should also be declared with LongPtr thus

Please Login or Register  to view this content.

[jeremyjelley]

Thank you for the edits. I made those changes and had to update these variables to get it to compile...

Please Login or Register  to view this content.

And then it crashed at the same spot again...

[xlnitwit]

Can you provide a workbook for testing?

[jeremyjelley]

Here you go...

[xlnitwit]

Thank you. It may be a while before I can look into it, as I only have 64bit Office at home and my family often think there are other more important things I should be doing.

[jeremyjelley]

Yeah, I understand

Fortunately this only crashes excel and not the whole system. I added a msgbox so you can break the code right before it crashes.

[jeremyjelley]

Hi again! I hope you had a wonderful holiday. Did you have a chance to check out that subclassing doc? I haven't been able to make any more progress with it.

[xlnitwit]

I'm afraid I did not- too many family commitments.

I will try my hardest to look at it tomorrow for you.

[xlnitwit]

OK, well I could not get that version to work. I believe it is due to the oft-cited issue about needing to reset the VB Editor when subclassing, so I found and adapted another piece of code posted by the aforementioned Mr. Tribak to suit the workbook you posted. For clarity I will provide each part as a separate post here

ThisWorkbook module

Please Login or Register  to view this content.

[xlnitwit]

Module CMouseMove_Module

Please Login or Register  to view this content.

[xlnitwit]

New module for API functions

Please Login or Register  to view this content.

[xlnitwit]

Class module CMouseMove

Please Login or Register  to view this content.

I have tested this lightly in Excel 2010 and 2016- both 64 bit- and it seems to be stable. I have also attached a workbook hereto.

[jeremyjelley]

That works great! I'll be able to implement it in my program next week and push it's limits. I'll let you know how goes.

Thank you!

[jeremyjelley]

I have being playing with it a bit. The subclassing seems to work well as long as it is the only workbook open, only one window open in that workbook and you're not doing anything else.

I have run into a couple problems so far...

1) The subclassing, while it is running, is inhibiting or sometimes preventing Formatting and Editing of Cells as well as Repainting.
Have you encountered this?

2) When opening another window in the View Tab the subclassing continues to work on the original Window but not the new one.
Do you know if there is a way to program it to be compatible with multiple windows?

3) Excel will sometimes crash when, while subclassing I switch between open windows and workbooks

[xlnitwit]

To begin with I should state that I have never had a need to actually use subclassing in Excel.

Addressing your points in order
1. I only did very limited testing of this application, I'm afraid
2. I wonder if this is an issue with the SDI interface in versions post 2010. It may be that there are multiple XLDESK windows, in which case each one would require subclassing.
3. Assuming there are error handlers in every routine that is triggered by the subclassing, I could not say for certain why that would be.

Unfortunately, debugging code like this takes a great deal of time- as it tends to crash the entire application rather than debug, as I'm sure you are aware- and that really isn't something I can do, I'm afraid. I'm not sure what the rules are here on such things, but I might suggest that you post at MrExcel and hope that Mr. Tribak takes up the challenge. He seems to have more expertise in this area than anyone I have come across in any forum. (do remember to add a link here if you do end up cross-posting) There is also a 'Call in the cavalry' thread here that I will use to see if I can garner assistance from any of the forum experts and gurus here.

[jeremyjelley]

I just found the article that discussed why activex doesn't work in multiple windows!

https://www.pcreview.co.uk/threads/a...indow.2751948/

Excel does not support the IViewObject interface.

Is it possible to identify which window does have activex control? If so I can just swap windows and sheets in the background.

It is a workaround but it will be a much easier solution than trying to get subclassing working.

[xlnitwit]

I don't personally know of any way to determine that. I assume you can't rely on it being the first workbook window?

[jeremyjelley]

The 'first workbook window' method is not completely reliable, no. That's how I am currently handling it though.

When it comes into an unknown situation I record the present windows configuration, close all but one window, and then reopen them all. This often works but is not ideal.