calling a fuction and passing arguments with .onaction

[blinkous]

Duuudes:
Have a question for the programmers. I have created a right-click popup menu with vba. On one of the "msoControlButtons" I want the on action procedure to call a function that requires arguments to be passed to it. The onaction procedure requires a string so this confuses me. Let me show an ex:

here's my menu button:

Set MenuItem = InventorPopUp.Controls.Add(msoControlButton)
With MenuItem
.Caption = "mm: (all)"
.OnAction = "Function1"
End With

here's my function:

Public Function Function1(arg1 As String, arg2 As String)

do some stuff

End Function

Now as you can see for the onaction I told it to run "function1" which works great if there are no arguments to pass, but I don't understand how to call the function and pass the arguments. I have tried several formats such as

.onaction = "Function1(string1, string2)"
.onaction = "Function1(""string1"", ""string2"")"
.onaction = "Function1 "string1", "string2""
.onaction = Function1 "string1", "string2"

nothing seems to work. VbA keeps giving me an "Argument not Optional" error. I know i could create as many functions as I need to to cover every possible argument but it would be much easier to pass the arguments. Any help would be greatly appreciated.

 

[enijhuis]

Thanks ITGabs, you really helped me out! Only your code works in 2007 (when you can't use control parameters)!
I did some more testing and it also works in Excel 2010 and Excel 2013.

 

[Saiya]

I have an example with string arguments:

   .OnAction =  "'" & ThisWorkbook.Name & "'!'Importer_Items_Temp_par_Menu_Déroulant " & Chr(34) & Nom_Fichier & Chr(34) & " , " & Chr(34) & Old_Val & Chr(34) & " , " & Chr(34) & ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value & Chr(34) & "'"

Where Importer_Items_Temp_par_Menu_Déroulant is a macro,
Nom_Fichier is a string variable,
Old_Val too,
ThisWorkbook.Sheets("Import_Objets").Cells(Item_Num, q * 2).Value is a string too, from a cell in a sheet.

 

[Ceeyee]

Thanks for this post!

a simple button
this is my code and work great in 2007

   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn

     .OnAction = "'someProcedure ""Param1"", ""Param2"" '"
   End With

notice the pattern (adding spaces to make it clear)
OnAction = " ' someProcedure " " Param1 " " , " " Param2 " " ' "

I hope this help, I did a bit of brute force trying to make the onAction pass the params

This is method will corrupt a xlsb file.

 

[Ceeyee]

You can pass argumentes with the .Parameter property, e.g.:

                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "Caption"
                    .faceId = 177
                    .OnAction = "'" & ThisWorkbook.Name & "'!" & "someProcedure"
                    .Parameter = "hello"
                End With

Old post for sure.
Regards

May I know how to use this method if I have more than one parameters?
Thanks.

 

[labrys]

I have been tearing my hair out.. after an hour or so of googling i could get no further, all i needed to get it to work were those two apostrophies!!
You sir are a genious!

I just did not see this ain anyone else's example. Mine would work without the parameteres, but not with, and I could not work out why.. Cheers!


.on action = "'{function/sub} ""param"" '"

Thanks for this post!

a simple button
this is my code and work great in 2007

   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn

     .OnAction = "'someProcedure ""Param1"", ""Param2"" '"
   End With

notice the pattern (adding spaces to make it clear)
OnAction = " ' someProcedure " " Param1 " " , " " Param2 " " ' "

I hope this help, I did a bit of brute force trying to make the onAction pass the params

 

[mPasGamer]

Duuudes:
Have a question for the programmers. I have created a right-click popup menu with vba. On one of the "msoControlButtons" I want the on action procedure to call a function that requires arguments to be passed to it. The onaction procedure requires a string so this confuses me. Let me show an ex:

here's my menu button:

Set MenuItem = InventorPopUp.Controls.Add(msoControlButton)
With MenuItem
.Caption = "mm: (all)"
.OnAction = "Function1"
End With

here's my function:

Public Function Function1(arg1 As String, arg2 As String)

do some stuff

End Function

Now as you can see for the onaction I told it to run "function1" which works great if there are no arguments to pass, but I don't understand how to call the function and pass the arguments. I have tried several formats such as

.onaction = "Function1(string1, string2)"
.onaction = "Function1(""string1"", ""string2"")"
.onaction = "Function1 "string1", "string2""
.onaction = Function1 "string1", "string2"

nothing seems to work. VbA keeps giving me an "Argument not Optional" error. I know i could create as many functions as I need to to cover every possible argument but it would be much easier to pass the arguments. Any help would be greatly appreciated.

I know this post is old but perhaps this will help someone else.
You can use a "=functionCall(parameter)" in the OnAction property
Below is an example.

Public Sub CreateShortcutMenu()
  On Error GoTo errHandler
  CommandBars("cmbPopUpMenu").Delete 'If commandbar exists, delete it; errHandler handles error when it doesn't exist
  Dim cmb As CommandBar, i As Long
  Set cmb = CommandBars.Add("cmbPopupMenu", msoBarPopup, False, False)
    With cmb
      i = 1
      .Controls.Add Type:=msoControlButton
      .Controls(i).Caption = "Button 1"
      .Controls(i).FaceId = 359 'Up Arrow face
      .Controls(i).OnAction = "=fxProcessOption('Button_1')"
      i = i + 1
      .Controls.Add Type:=msoControlButton
      .Controls(i).Caption = "Button 2"
      .Controls(i).FaceId = 360 'Down Arrow face
      .Controls(i).OnAction = "=fxProcessOption('Button_2')"
    End With
  Set cmb = Nothing
ExitSub:
  Exit Sub
errHandler:
  If Err.Number = 5 Then Resume Next 'cannot delete CommandBar doesn't exist, resume next statement
  MsgBox "Error in " & Application.VBE.ActiveCodePane.codemodule & ".CreateShortcutMenu: " & Err.Number & " - " & Err.Description
  Resume ExitSub
End Sub 'CreateShortcutMenu

Public Function fxProcessOption(pAction As String) As Boolean
  On Error GoTo errHandler
    fxProcessOption = False
  Select Case pAction
    Case Is = "Button_1"
      MsgBox "Add Code to Process Button 1"
    Case Is = "Button_2"
      MsgBox "Add Code to Process Button 2"
  End Select
ExitSub:
    fxProcessOption = True
  Exit Function
errHandler:
  MsgBox "Error in " & Application.VBE.ActiveCodePane.codemodule & ".fxProcessOption: " & Err.Number & " - " & Err.Description
  Resume ExitSub
End Function 'fxProcessOption