how to add tooltips to excel udfs i created?

[NA_AB]

hey ppl, in my project which is actually developing a COM addin to ms excel
using c#.net, I am creating my UDFs which are working fine.

But now, I want to be able to show tooltips to these UDFs whenever some one
starts using them in an excel cell in the form of '=my_udf(......)'. I want
to display a tooltip that talks about the number and type of the arguments
'my_udf( )' takes.

Can anyone guide me through this?

Regards,
NA_AB

 

[Peter T]

AFAIK there is no direct way to do that. To work around with an alternative,
the main problem is code that automates Excel is not asynchronous, which
means while the cell is in Edit mode all code is suspended.

Need look at what's currently in the edit bar, do all the stuff to parse
looking for your UDF, then display a tooltip in the right screen place, and
all that while in Edit mode.

I'm not saying the task is impossible but I'm not aware of anyone having
done it. Even without the Edit mode problem there is still a lot to do.
Unless some 'back-door' way in is exposed it would be quite a task. Thinking
aloud, would need to sub-class window events of the edit bar and go from
there; maybe doable if someone's up for the challenge, but I think I'll
pass!

Regards,
Peter T

 

[NA_AB]

hey peter, many times I came across this:

http://longre.free.fr/english/index.html

Laurent Longre's website for FUNCUSTOMIZE add-in... but it's again vb! can
yu kindly look into this and tell me what it says?! am not gettin it
100percent.. does it solve my problem?!

Regards,
na_ab

 

[Peter T]

The original approach developed by Laurent Longre enabled argument
descriptions in the function wizard. This was further (and cleverly)
improved by 'KeepItKool' to overcome the 255 XL4Macro string limit which
severely restricted the overall available info that could be displayed,
particularly with many or complex arguments..

http://www.jkp-ads.com/articles/RegisterUDF01.asp

This approach can usefully be adapted for use in a ComAddin and/or
Automation addin albeit there is no way to prevent the functions appearing
in two places (categories) in the wizard.

However, whilst both approaches will display info in the wizard, neither
will do anything whatsoever to help display UDF argument tooltips while in
edit mode.

Why not look into converting the method for C#, all the VBA code is freely
available. I strongly suspect you will find that more productive than
looking into tooltips, not that I want to put you off!

Regards,
Peter T

 

[NA_AB]

Ohk, does it mean, there is no way i can get the tooltips? I did go through
the post yu mentioned Peter... Actually, in my project, user is expected to
simply use my UDFs and so, am a lil keen about tooltips than about giving
function descriptions.

Thanks and Regards,
na_ab

 

[Peter T]

I hesitate to say "no way" but I've never heard of anyone doing it.

Actually I did have another quick look after posting my previous comments as
I can already display a tooltip in correct location, even while in edit
mode. But the main problem is I cannot find any way to read the text of
what's being typed in the edit cell (or input bar), it does not appear
visible to normal API methods, at least not in the window I have been
looking in (classname "EXCEL6").

I don't understand what you mean by "lil keen etc", however I would have
thought function and argument descriptions would be useful, perhaps with a
link to a chm help file as well.

Regards,
Peter T

 

[NA_AB]

oops sorry, lil was actually 'little', am sorry i wasn't clear enough. Yes
Peter, even I was wondering how to extract or read what is being typed
currently.

I can already display a tooltip in correct location, even while in edit
mode.

What did this mean? Am sorry but am not getting what exactly you were saying.

Regards,
na_ab

 

[Peter T]

Can display a tooltip (a window with some text, even a say a captionless
userform if coding in VBA) to appear next to a cell while mouse hovers over
the cell. But that doesn't help you.

To fulfil your objective need to do all the following -
1 Identify when user enters edit mode
2 Read the text and cursor position as user edits the cell or input bar (the
"edit" window)
3 Parse the text looking for a open bracket preceded by your udf name
then which argument is the cursor in, etc
4 Work out what, if any, info text to display
5 Work out where to position and size the tool tip window, ideally in a rich
text box
6 Monitor when to update or clear the tooltip

And that's all there is to it!

Main problem is "2".
Can read the key strokes into the edit window (ie watch characters as they
are typed and any other events) but that's not enough, or at least still a
lot more to do. (For any one curious who has Spy++, find "EXCEL6" and watch
the "messages" as you edit the cell).

Regards,
Peter T