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! |
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! | |
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 |
All times are GMT +1. The time now is 06:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com