Results 1 to 5 of 5
  1. #1
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    Tool tip for a UDF

    With a user defined function, anyone know of a way to display a "tool tip box" commenting what the funtion does and the paramters that is accepts? It would provide users with information on how to use the function properly.

    Tool tip when entering a function into the formula bar
    Function1.jpg

    Tool tip to show accepted parameters
    Function2.jpg

    Thanks,
    Maud

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I don't exactly have a solution, but here are some things to muse on:

    1. I am pretty sure that you cannot get "into" the Tooltip dialog, but does this get you part-way to your objective; http://www.jkp-ads.com/Articles/RegisterUDF00.asp

    2. A
    nother route to investigate: after typing the UDF name into a cell, if you press CTRL SHIFT A the parameters of the UDF are displayed. As a first step, giving the parameters informative variable names in the UDF declaration helps a lot.

    3. I was wondering if you could use an Event-trapping routine to bring up the information in 2. above automatically as the user types but I am not aware of any way of trapping events that occur when in Edit mode.

    PS Even creating a UDF as an Add-in, using XLL, offers no opportunity to create the Tooltip you are after :-(
    Last edited by MartinM; 2013-01-05 at 05:41.

  3. The Following User Says Thank You to MartinM For This Useful Post:

    Maudibe (2013-01-05)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Martin,
    Retired Geek had written some neat code to display the purpose of the UDF and the accepted arguments within the function wizard and had pointed me to that site. I was not, however, able to find a way to create the tips outside of the funtion wizard on the spreadsheet. Your tip of CTRL SHIFT A works just fine as a work around.

    Many thanks
    Maud

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Thanks for the credit but I didn't write the code I got it from the article here.
    Code:
    Option Explicit
    
    Public Function zGetFileNameExt(zFullPath As String) As String
    
       Dim vResults As Variant
       
       vResults = Split(zFullPath, "\")
       
       zGetFileNameExt = vResults(UBound(vResults))
       
    End Function
    
    Sub RegisterUDF()
    
        Dim s As String
        
        s = "Extracts the Filename.ext from a fully qualified d:\path\filename.ext string." & vbLf _
        & "zGetFileNameExt(<full path string | cell reference>)"
    
        Application.MacroOptions Macro:="zGetFileNameExt", Description:=s, Category:="User Defined"
        
    End Sub
    
    Sub UnregisterUDF()
        Application.MacroOptions Macro:="zGetFileNameExt", Description:=Empty, Category:=Empty
    End Sub
    Register UDF.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2013-01-05)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    RG,
    I found that you had to run the RegisterUDF() to enable the tips to show. Just a note if someone wants to use your code.

    Thanks again,
    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •