Creating UDF prompts (XL 2007)

D

David

I'm trying to get hints/prompts to display for a UDF I've written.

This group had a thread back in 2007 that had the answer, but the link
I found is now dead. Here's the question/answer I found and the old
link. Can anybody provide and answer or a link to the archived
answer.

TIA,
David

------------------------------------------------------------------------------------------
http://groups.google.com/group/micr...=udf+screentips+group:*Excel#106f0180ce2819d5
--
Gary''s Student - gsnu2007a


michael.beckinsale said:
Hi All,

With XL2003 built in functions you get prompts for the various
parameters / arguments as you type into the cell.

eg typing =Sumif( directly into a cell results in this prompt:

SUMIF(range, criteria,[sumrange])

Can this be achieved with UDF and if so how?

Regards

Michael B.

---------------------------------------------------------------------------------------------------------------------------
 
G

Gary''s Student

The Google link is still active:

Here is the response Bob Phillips posted:

From: "Bob Phillips" <[email protected]>
Date: Mon, 9 May 2005 10:02:35 +0100
Local: Mon, May 9 2005 5:02 am
Subject: Re: Excel 2002 UDF screen tips
Reply to author | Forward | Print | Individual message | Show original |
Report this message | Find messages by this author
There are a couple of ways, one not too robust, one that requires a dll.

The not too robust way, suggested by Laurent Longre and working for two
parameters is :-
Const Lib = """c:\windows\system\user32.dl­­l"""
Option Base 1


Private Function Multiply(N1 As Double, N2 As Double) As Double
Multiply = N1 * N2
End Function


'=============================­­=============


Private Function Divide(N1 As Double, N2 As Double) As Double
Divide = N1 / N2
End Function


'=============================­­=============


Sub Auto_open()


Register "DIVIDE", 3, "Numerator,Divisor", 1, "Division", _
"Divides two numbers", """Numerator"",""Divisor """, "CharPrevA"
Register "MULTIPLY", 3, "Number1,Number2", 1, "Multiplication", _
"Multiplies two numbers", """First number"",""Second number """, _
"CharNextA"


End Sub


'=============================­­=============


Sub Register(FunctionName As String, NbArgs As Integer, _
Args As String, MacroType As Integer, Category As String, _
Descr As String, DescrArgs As String, FLib As String)


Application.ExecuteExcel4Macro _
"REGISTER(" & Lib & ",""" & FLib & """,""" & String(NbArgs, "P") _
& """,""" & FunctionName & """,""" & Args & """," & MacroType _
& ",""" & Category & """,,,""" & Descr & """," & DescrArgs & ")"


End Sub


'=============================­­=============


Sub Auto_close()


Dim FName, FLib
Dim I As Integer
FName = Array("DIVIDE", "MULTIPLY")
FLib = Array("CharPrevA", "CharNextA")
For I = 1 To 2
With Application
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
.ExecuteExcel4Macro "REGISTER(" & Lib & _
",""CharPrevA"",""P"",""" & FName(I) & """,,0)"
.ExecuteExcel4Macro "UNREGISTER(" & FName(I) & ")"
End With
Next


End Sub


The dll solution:
Also from Laurent Longre, you use the FUNCUSTOMIZE utility .Get it at
http://longre.free.fr/english/


--
HTH


Bob Phillips

--
Gary''s Student - gsnu200907


David said:
I'm trying to get hints/prompts to display for a UDF I've written.

This group had a thread back in 2007 that had the answer, but the link
I found is now dead. Here's the question/answer I found and the old
link. Can anybody provide and answer or a link to the archived
answer.

TIA,
David

------------------------------------------------------------------------------------------
http://groups.google.com/group/micr...=udf+screentips+group:*Excel#106f0180ce2819d5
--
Gary''s Student - gsnu2007a


michael.beckinsale said:
Hi All,

With XL2003 built in functions you get prompts for the various
parameters / arguments as you type into the cell.

eg typing =Sumif( directly into a cell results in this prompt:

SUMIF(range, criteria,[sumrange])

Can this be achieved with UDF and if so how?

Regards

Michael B.
 
P

Peter T

Depends what you mean by hints/prompts, AFAIK there is no utility out there
that will help you add tooltips or autocomplete to your UDFs.

For info and help text in the function wizard go for the link given by Gord
Dibben. If you need to include more that total 255 characters of info text,
a brilliant method is described here (not easy though)

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

Regards,
Peter T

David said:
I'm trying to get hints/prompts to display for a UDF I've written.

This group had a thread back in 2007 that had the answer, but the link
I found is now dead. Here's the question/answer I found and the old
link. Can anybody provide and answer or a link to the archived
answer.

TIA,
David

------------------------------------------------------------------------------------------
http://groups.google.com/group/micr...=udf+screentips+group:*Excel#106f0180ce2819d5
--
Gary''s Student - gsnu2007a


michael.beckinsale said:
Hi All,

With XL2003 built in functions you get prompts for the various
parameters / arguments as you type into the cell.

eg typing =Sumif( directly into a cell results in this prompt:

SUMIF(range, criteria,[sumrange])

Can this be achieved with UDF and if so how?

Regards

Michael B.

---------------------------------------------------------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top