Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open "Function Argument Window" using a macro
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open "Function Argument Window" using a macro
Look he
http://www.jkp-ads.com/Articles/RegisterUDF00.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "trembl27" wrote in message oups.com... | 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! | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open "Function Argument Window" using a macro
On 26 juil, 13:51, "Niek Otten" wrote:
Look he 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").Delet e Set cbMainMenuBar = Application.CommandBars(1) iHelpMenu = cbMainMenuBar.Controls("?").Index Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Befo=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"page number" function as an argument | Excel Discussion (Misc queries) | |||
Pivot Table Creation Macro fails with Error #5 "Invalid procedure call or argument" | Excel Programming | |||
How to change the font of Display window of "Open File" | Excel Discussion (Misc queries) | |||
Use Macro to open "Find" window | Excel Programming | |||
Using a "File-Open"-Window in a Excel Macro? | Excel Programming |