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
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