How to attach function to a custom Menubar?

[Malcom Jazz]

Hi Rob van Gelder,
Thank you very much. But I am afraid that my concern for function i
still not addressed. I think you are again right. I am refering to th
custom formula function. In your modified code, you have used the o
action property to launch a macro called Button_Click. This is not wha
I want.

Lets say I have a following function in a standerd bas module.

Function Growth(rn As Range)
Growth = rn.Value + 10
End Function

Now I want to access this function from the menu button1 (withou
typing the function) This button, on action, runs a macro buton_clic
with parameter1.
In my case,
When user clicks on the button, it should show the popup for abov
function which accepts a range argument. You can select a range with
mouse and click on OK. This will insert the formula something lik
=Growth(A3) in the activecell and value will be displayed as value o
A3 +10.

I hope I have made myself clear. I should have given this explanatio
earlier.
Sorry for the inconvinience.

Regards,
Malco

 

[Rob van Gelder]

You have the code for attaching to a commandbar, so I wont go over that
again.

Application.InputBox is magic (not to be confused with plain InputBox
without the Application. qualifier)

Sub Button_Click()
Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Cell?", Type:=8)
If Err.Number <> 0 Then Selection.Formula = "=Growth(" & rng.Address &
")"
End Sub