Open "Function Argument Window" using a macro

[trembl27]

Hi!

I developped an add-in containing a toolbox of functions to perform
some calculations.

However, I would like to create a kind of "easy-access" GUI for these
and the way I found was to create a new menu item with sub-menu/items
and the like.

However, my toolbox is for functions, not macros, so I cannot simply
run my function without any arguments when I select the item.
Therefore, I would like to open a user form with the options for this
function, just like the "Function Argument Window" that opens when we
use the standard function button on the menu. (I'm talking about the
menu with the arguments/tooltips)

Is there any way to call this "Window Argument Form" directly and
supply my function in argument and have it work just like if I clicked
on the main function button? If I can avoid creating a user form I
will as it's not really needed to have custom forms for my functions.

Thanks!

 

[Niek Otten]

Look here:

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi!
|
| I developped an add-in containing a toolbox of functions to perform
| some calculations.
|
| However, I would like to create a kind of "easy-access" GUI for these
| and the way I found was to create a new menu item with sub-menu/items
| and the like.
|
| However, my toolbox is for functions, not macros, so I cannot simply
| run my function without any arguments when I select the item.
| Therefore, I would like to open a user form with the options for this
| function, just like the "Function Argument Window" that opens when we
| use the standard function button on the menu. (I'm talking about the
| menu with the arguments/tooltips)
|
| Is there any way to call this "Window Argument Form" directly and
| supply my function in argument and have it work just like if I clicked
| on the main function button? If I can avoid creating a user form I
| will as it's not really needed to have custom forms for my functions.
|
| Thanks!
|

 

[trembl27]

Look here:

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

What I could find on that website was how to give a description/
tooltip to my custom function. This is not what I was meaning so I'll
show my problem with a code sample.

I add a few menus with this when I add my xla file to Excel :

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

On Error Resume Next
Application.CommandBars(1).Controls("&MCPH").Delete
Set cbMainMenuBar = Application.CommandBars(1)
iHelpMenu = cbMainMenuBar.Controls("?").Index
Set cbcCutomMenu =
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
cbcCutomMenu.Caption = "&MCPH"

'Add Item
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "EVLSJ_ELEV"
.OnAction = "mcph.xla!OpenFunctionArgumentWindow"
End With

'Add Item
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "EVLSJ_ELEV2"
.OnAction = "mcph.xla!
OpenFunctionArgumentWindow2(""EVLSJ_ELEV"")"
End With

On Error GoTo 0
End Sub

Public Sub OpenFunctionArgumentWindow()
MsgBox "In OFAW"
ActiveCell.Formula = "=EVLSJ_ELEV()"
Application.Dialogs(xlDialogFunctionWizard).Show
End Sub

Public Sub OpenFunctionArgumentWindow2(mFunction As String)
MsgBox "In OFAW2"
ActiveCell.Formula = "=" & mFunction & "()"
Application.Dialogs(xlDialogFunctionWizard).Show
End Sub

As you can see in the code, I put a onAction of
OpenFunctionArgumentWindow and one on OpenFunctioNArgumentWindow2.
When I click on the menu item that does not pass any argument, the
formula =EVLSJ_ELEV() is put in the cell and the Function Argument
Window is opened. However, with OpenFunctionArgument2 the formula is
set correctly but the function argument window does not open. Also,
we can see the msgbox twice!

Anyone has a solution?

Thanks a lot!

François Tremblay