Create Excel ScreenTips for a custom VBA function

I would like to have a ScreenTips message show up when the Excel user begins
to type a function I created in VBA. The message would explain what the
arguements in the function are, much the same way the built in functions such
as SUM and AVERAGE do when you start entering them. I'm aware of comment and
data validation notes that can appear when a cell is selected, but I want
something that will appear when the function is being typed into any cell in
a given workbook.
Thanks.

Try something like:

Place this in the WorkSheet Code:
-------------------------------------------------------------------------------
Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range("A1") ' <==== Change this ti SUIT your needs
If Not Application.Intersect(Target, MyRange) Is Nothing Then Call Macro2
End Sub
--------------------------------------------------------------------------------

Place the below in a Module:
=====================================================
Sub Macro2()
Application.DisplayAlerts = True
res = MsgBox("Place the Message you what here....", , "Place a msgbox TITLE
here")
End Sub

=====================================================

If the user selects the selected cell (A1) then the message will display.


Corey....

Not sure what you mean by ScreenTip;
- Something to do the Mac version ?
- Using the Assistant ?
- Excel 2002 or higher ?
I don't use any of these, but...
You can add a description to your function. Go to the Object Browser, select
VBA Project, navigate to your function, right-click, Properties and enter
the text you want.
This description appears when you click the "=" to the left of the formula
bar.

NickHK


"Ebers" <Ebers@discussions.microsoft.com> wrote in message
news:B15C0539-E0CE-4BEF-B05D-78C6F794C05B@microsoft.com...
> I would like to have a ScreenTips message show up when the Excel user
begins
> to type a function I created in VBA. The message would explain what the
> arguements in the function are, much the same way the built in functions
such
> as SUM and AVERAGE do when you start entering them. I'm aware of comment
and
> data validation notes that can appear when a cell is selected, but I want
> something that will appear when the function is being typed into any cell
in
> a given workbook.
> Thanks.

Apologies for hanging this on your post Nick, but don't have the original and
too lazy bring it back.

Ebers

To fully customize your User Defined Functions....

See Laurent Longre's website for FUNCUSTOMIZE add-in.

ZIP file also includes a demo file and a how-to-use file.

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

UNFOTUNATELY................Ballon/Screen Tips are not available with Laurent's
method.


Gord Dibben Excel MVP

On Wed, 26 Jul 2006 10:47:50 +0800, "NickHK" <TungCheWah@Invalid.com> wrote:

>Not sure what you mean by ScreenTip;
>- Something to do the Mac version ?
>- Using the Assistant ?
>- Excel 2002 or higher ?
>I don't use any of these, but...
>You can add a description to your function. Go to the Object Browser, select
>VBA Project, navigate to your function, right-click, Properties and enter
>the text you want.
>This description appears when you click the "=" to the left of the formula
>bar.
>
>NickHK
>
>
>"Ebers" <Ebers@discussions.microsoft.com> wrote in message
>news:B15C0539-E0CE-4BEF-B05D-78C6F794C05B@microsoft.com...
>> I would like to have a ScreenTips message show up when the Excel user
>begins
>> to type a function I created in VBA. The message would explain what the
>> arguements in the function are, much the same way the built in functions
>such
>> as SUM and AVERAGE do when you start entering them. I'm aware of comment
>and
>> data validation notes that can appear when a cell is selected, but I want
>> something that will appear when the function is being typed into any cell
>in
>> a given workbook.
>> Thanks.
>

Gord Dibben MS Excel MVP

Thanks everyone, the replies were helpful but unfortunately I still haven't
figured out how to do exactly what I want. I believe Corey's code returns a
message box if a given cell is selected, but what i need is the user to be
able to choose any cell and type in "=" and the function name and have a
ScreenTips/Tooltips/balloon message pop up somewhere on the screen,
describing how to use the function.

Nick's idea seemed like it was on the right track, but after following the
steps you gave, I was unable to get any result. I got lost when you said
click the = to the left of the formula bar and the message will appear. I
couldn't find any way for the description I entered in the properties to
appear anywhere.

If you're unclear on what I'd like, open excel and pick a cell and type
"=sum(" and the yellow message that pops up explaining the arguments in the
sum function is what i'd like to do for my user defined function.
Thanks again for the help

"Ebers" wrote:

> I would like to have a ScreenTips message show up when the Excel user begins
> to type a function I created in VBA. The message would explain what the
> arguements in the function are, much the same way the built in functions such
> as SUM and AVERAGE do when you start entering them. I'm aware of comment and
> data validation notes that can appear when a cell is selected, but I want
> something that will appear when the function is being typed into any cell in
> a given workbook.
> Thanks.

Thanks everyone, the replies were helpful but unfortunately I still haven't
figured out how to do exactly what I want. I believe Corey's code returns a
message box if a given cell is selected, but what i need is the user to be
able to choose any cell and type in "=" and the function name and have a
ScreenTips/Tooltips/balloon message pop up somewhere on the screen,
describing how to use the function.

Nick's idea seemed like it was on the right track, but after following the
steps you gave, I was unable to get any result. I got lost when you said
click the = to the left of the formula bar and the message will appear. I
couldn't find any way for the description I entered in the properties to
appear anywhere.

If you're unclear on what I'd like, open excel and pick a cell and type
"=sum(" and the yellow message that pops up explaining the arguments in the
sum function is what i'd like to do for my user defined function.
Thanks again for the help

"Ebers" wrote:

> I would like to have a ScreenTips message show up when the Excel user begins
> to type a function I created in VBA. The message would explain what the
> arguements in the function are, much the same way the built in functions such
> as SUM and AVERAGE do when you start entering them. I'm aware of comment and
> data validation notes that can appear when a cell is selected, but I want
> something that will appear when the function is being typed into any cell in
> a given workbook.
> Thanks.

Thanks everyone, the replies were helpful but unfortunately I still haven't
figured out how to do exactly what I want. I believe Corey's code returns a
message box if a given cell is selected, but what i need is the user to be
able to choose any cell and type in "=" and the function name and have a
ScreenTips/Tooltips/balloon message pop up somewhere on the screen,
describing how to use the function.

Nick's idea seemed like it was on the right track, but after following the
steps you gave, I was unable to get any result. I got lost when you said
click the = to the left of the formula bar and the message will appear. I
couldn't find any way for the description I entered in the properties to
appear anywhere.

If you're unclear on what I'd like, open excel and pick a cell and type
"=sum(" and the yellow message that pops up explaining the arguments in the
sum function is what i'd like to do for my user defined function.
Thanks again for the help

"Ebers" wrote:

> I would like to have a ScreenTips message show up when the Excel user begins
> to type a function I created in VBA. The message would explain what the
> arguements in the function are, much the same way the built in functions such
> as SUM and AVERAGE do when you start entering them. I'm aware of comment and
> data validation notes that can appear when a cell is selected, but I want
> something that will appear when the function is being typed into any cell in
> a given workbook.
> Thanks.

Such a "feature" is not available in my version of Excel (2K), so I can't
help you there.
I was explaining:
1. Enter "=Sum(" in the formula bar.
2. Move the cursor so it is somewhere in "Sum"
3. Click the "=" just next to the red cross and green tick.
You can get a description there using Gord's suggestion.

NickHK


"Ebers" <Ebers@discussions.microsoft.com> wrote in message
news:05677DD5-30DB-4B41-B543-E4AE49C02338@microsoft.com...
> Thanks everyone, the replies were helpful but unfortunately I still
haven't
> figured out how to do exactly what I want. I believe Corey's code returns
a
> message box if a given cell is selected, but what i need is the user to be
> able to choose any cell and type in "=" and the function name and have a
> ScreenTips/Tooltips/balloon message pop up somewhere on the screen,
> describing how to use the function.
>
> Nick's idea seemed like it was on the right track, but after following the
> steps you gave, I was unable to get any result. I got lost when you said
> click the = to the left of the formula bar and the message will appear. I
> couldn't find any way for the description I entered in the properties to
> appear anywhere.
>
> If you're unclear on what I'd like, open excel and pick a cell and type
> "=sum(" and the yellow message that pops up explaining the arguments in
the
> sum function is what i'd like to do for my user defined function.
> Thanks again for the help
>
> "Ebers" wrote:
>
> > I would like to have a ScreenTips message show up when the Excel user
begins
> > to type a function I created in VBA. The message would explain what the
> > arguements in the function are, much the same way the built in functions
such
> > as SUM and AVERAGE do when you start entering them. I'm aware of
comment and
> > data validation notes that can appear when a cell is selected, but I
want
> > something that will appear when the function is being typed into any
cell in
> > a given workbook.
> > Thanks.