How to attach function to a custom Menubar?
 

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

--
Malcom Jaz
-----------------------------------------------------------------------
Malcom Jazz's Profile: http://www.excelforum.com/member.php...fo&userid=1587
View this thread: http://www.excelforum.com/showthread.php?threadid=27360

How to attach function to a custom Menubar?
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Malcom Jazz" wrote in message
...

Hi Rob van Gelder,
Thank you very much. But I am afraid that my concern for function is
still not addressed. I think you are again right. I am refering to the
custom formula function. In your modified code, you have used the on
action property to launch a macro called Button_Click. This is not what
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 (without
typing the function) This button, on action, runs a macro buton_click
with parameter1.
In my case,
When user clicks on the button, it should show the popup for above
function which accepts a range argument. You can select a range with a
mouse and click on OK. This will insert the formula something like
=Growth(A3) in the activecell and value will be displayed as value of
A3 +10.

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

Regards,
Malcom


--
Malcom Jazz
------------------------------------------------------------------------
Malcom Jazz's Profile:
http://www.excelforum.com/member.php...o&userid=15872
View this thread: http://www.excelforum.com/showthread...hreadid=273601