Create Excel ScreenTips for a custom VBA function

[Guest]

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.

 

[Corey]

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

 

[NickHK]

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

 

[Gord Dibben]

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

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

Gord Dibben MS Excel MVP

 

[Guest]

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

 

[NickHK]

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

 

[NA_AB]

hey Gord,

I am not using VB, but C#, cna yu suggest me how to achieve the same using
c#???

 

[NA_AB]

hi Ebers, any luck in this regard? am tryin to implement the same...