Making a macro more dynamic.

I wanted to create a "mouse over tool tip", which displays a userform when the mouse is hovering over a label on my worksheet, and then closes the userform when the mouse moves away.

So far I have managed to achieve this using POINTAPI to create MouseX and MouseY functions to detect the location of the mouse with the "Label1_MouseMove" event.

Please Login or Register  to view this content.

My issue now is how do I a) make this same macro apply to all labels on the worksheet? And b) How can I get the screen coordinates for the location of the control?

At present I have had to manually work out the location of the control. Using ".Top" and ".Left" only returns the position inside of the worksheet, not the location on the screen itself.

If anyone has any ideas on either of these issues, it would be greatly appreciated!

Originally Posted by PrizeGotti

My issue now is how do I a) make this same macro apply to all labels on the worksheet? And b) How can I get the screen coordinates for the location of the control?

ad a):
I think you actually don't want that, since you want a pop-up on hovering over a label, so any current pop-up (if any ...) needs to be hide first.
ad b):
Imo you don't need to if you introduce an extra (transparent) label in the background, of which its mouse move event handler takes care of hiding the pop-up.

Maybe I could have been more clear, I'll give it a try.

The MouseMove event handler is executed when you hover the mouse over the label. You're wanting to use the exact screen coordinates to determine when the mouse is NOT hovering over a label. If you use an extra label that is the size of the UsedRange of your worksheet, the mouse will move over that label when it exits a pop-up label. Then you don't need the screen coordinates at all.
Of course, the event handlers of all labels must, in addition to showing the pop-up, also make the extra label accessible to the mouse, while the event of that extra label then makes this same label inaccessible, so that the user can select worksheet ranges again.

Since you don't need to know the screen location of each label, you can provide one macro which can be called from each mouse move event handler, except for the event handler of the extra label.


EDIT: forgot to mention that with this approach a continue executing DoEvents loop isn't needed anymore...

You are welcome and thanks for posting back.

Note that it may be necessary for the MouseMove event handler to check beforehand whether the pop-up is already displayed in order to avoid screen flickering.