PDA

View Full Version : Pop up a help text on userform after hovering the mouse for 1 sec



JimmyTheHand
01-24-2019, 01:41 PM
Hi Guys,
hope you can help me with this.

I want to make an Excel userform where, if the user hovers the mouse cursor over a CommandButton for 1 sec or so, then a help message appears with the description of the Button's function. The built-in ControlTipText is not sufficient, because the description would be faaar too long for that. I want it to work primarily in Excel 2016. If it's compatible with other versions, that's a bonus.

Q1: Any ideas how to accomplish that?

I thought the best (or even only) way would be to have some kind of timer function, which is activated by the MouseMove event of the Button, deactivated by the MouseMove event of the Form itself, and if the timer counts down without being deactivated, then a 2nd userform would pop up with the help text.

Q2: Do you know of any reliable timer function/control/whatever, preferably as part of Office or of Windows, that could be used for this purpose?

I know about the Application.OnTime method, which could be used, but I don't think it's reliable, and my past experience warns me to keep away from it. Also, a few years back I used the IETimer.ocx, which was reputedly part of Internet Explorer, but it has long been removed, and now I cannot find it anywhere, any download links I found were broken. Also, I know of the Timer event of Access forms, which would be perfect, but I have no idea how to use them in conjunction with my Excel form.

So I looked for a suitable 3rd party ActiveX timer control, and this is what I found: https://www.nsbasic.com/s309/Timer/
I was able to utilize it at home, in my Win7 / Excel 2007 environment. I did not register it with regsvr32, but added a reference directly to the ocx file. Then the control appeared in the list of "Additional Controls", and I could add it to the userform, and it worked fine.

However, I couldn't repeat the same process on my company PC, where I have Win7 / Office 2016. I added the reference to the ocx file, and it was listed among the References, but the timer control itself did not show up among the "Additional Controls". (I checked it at least 10 times.)

Q3: Any ideas how/why this could happen?

I can think of 2 reasons: it's 1) either the office version difference, or 2) the IT security restrictions used in my company.
I think No. 2 is more likely but, unfortunately, I have no other access to Excel 2016, so I cannot rule out the No. 1.

Q4: Can someone please check and confirm that the above linked ocx file works with Excel 2016 or not?

Let's assume that it works and the source of my problem is the IT sec. settings. I have no delusions that I would ever win the support of our IT for this, because they are as flexible as a rock. Registering the ocx is also not an option, because the workbook should be portable. (And the IT dept. would not support it anyway.)

Q5: So, is there any method of using/invoking/manifesting/etc. an ActiveX control on your userform, other than picking it up from the "Additional Controls" list?

I'm open to any suggestions, feel free to comment.
Also please keep in mind that my main question is Q1. If you can answer that, then Q2-Q5 become obsolete.

Thanks,
Jimmy

Paul_Hossler
01-24-2019, 04:38 PM
It might be better to just keep it simple(r) and add a 'Get Help' button

You can tie it in to various MouseMove events to make it easier

23627

Give the attachment a shot - it's not nearly as elegant as you wanted with the one second delay, but is simpler

The popup goes away if you mouse over the userform, click the command button, or click the popup

JimmyTheHand
01-24-2019, 11:19 PM
Thank you :)
Yeah, I've tried/accomplished this approach (i.e. with separate 'Help' images), it really made things simpler. The reason why I'm looking for an alternative is that in my form there are actually not just 1 command button, but 17 different controls for which I want to display help messages. The placement of 17 help images is problematic, because

they need a lot of extra space (space is critical because of the limited size of company notebook screens)
they mess up the general look of the form
it is confusing which help button belongs to what control

Probably I could dismiss all the above excuses by properly redesigning the form, using tabs or something, and will definitely make the attempt if there is no other way. But I like challenges of the intellect better than challenges of the sitting muscles :yes.

mikerickson
01-25-2019, 08:56 AM
How about one Help button.
MouseMove over a control, it becomes visible and moves to that control. The .Tag property of the help button gets the name of the control that is being hovered.
MouseMove over the UF, Help becomes invisible.

When clicked, the Help button looks to its .Tag property to decide which help text to show.

(I'm at work and only have time to pass an idea. I'll post a more developed wb later.)

JimmyTheHand
01-25-2019, 10:12 AM
How about one Help button.
MouseMove over a control, it becomes visible and moves to that control. The .Tag property of the help button gets the name of the control that is being hovered.
MouseMove over the UF, Help becomes invisible.

When clicked, the Help button looks to its .Tag property to decide which help text to show.

(I'm at work and only have time to pass an idea. I'll post a more developed wb later.)

Your idea set my imagination into motion...
I will try to solidify it later during the weekend. Thanks :thumb

Aflatoon
01-28-2019, 04:18 AM
You could also use a right-click on the button(s) to pop up your help info, using the MouseUp event and testing for Button = 2.