calling a fuction and passing arguments with .onaction

B

blinkous

New Member
Joined
Jun 17, 2003
Messages
6
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
I don't think you can call a procedure with arguments from a button. You will need to assign it a procedure and call your function from there, with the appropriate arguments.
 
Upvote 0
Have you tried this yet?
Code: 
.onaction = Function1(string1, string2)

Even though strings are represented with "" 's, you dont have to put them around the variable containing the string. Hope this helps. Sorry if this does not work as I have been away from this board for like 2 months now:)
 
Upvote 0
Thanks for the replies guys.
To Andrew:
You have a pretty simple solution to the problem that I actually didn't think about. Preciate it dude.

To Challseus:
Yes, I tried that and it didn't work. Still seems like you could do this someway though. Preciate your help.

Later
 
Upvote 0
This seemed to work ok for me:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Test(Arg1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Arg2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
    MsgBox Arg1 & vbCrLf & Arg2
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> AssignIt()
    <SPAN style="color:#00007F">With</SPAN> Application.CommandBars("Cust1").Controls(1)
        .OnAction = "Test(" & Chr(34) & "First line" & Chr(34) & "," & Chr(34) & "Second Line" & Chr(34) & ")"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
I thought MS broke the foll. in 2002 but in my tests before this post it worked! So, either it's been fixed or its broken only under certain conditions. Also note that this is an undocumented feature and if broken MS is highly unlikely to fix it.

While the solution below uses a programmatic approach, you can also assign a macro through the GUI. Just leave out the outermost set of double quotes.

The solution is courtesy Tom Ogilvy, a fellow MS MVP. Search google.com for more on the subject http://www.google.com/groups?as_q=o...&oe=UTF-8&as_uauthors=ogilvy&lr=&num=20&hl=en.

Basically, you need the right number of single and double quotes to pull it off.
Code: 
Option Explicit
Sub Macro1()
    ActiveSheet.Shapes("Button 1").OnAction = "'Testit ""a"",""b""'"
    ActiveSheet.Shapes("Button 2").OnAction = "'Testit2 1,2'"
    End Sub
and associated functions
Code: 
Option Explicit

Function testIt(x, y)
    MsgBox x & ", " & y
    End Function
Function testIt2(x, y)
    MsgBox x / y
    End Function
blinkous said:
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.
Click to expand...
 
Upvote 0
Thanks a lot tusharm. You've saved my life!

Anyways, I think I've added some important stuff for beginners!

DEFINE COMMAND BAR CONTROL
With Application.CommandBars("Custom").Controls.Add(Type:=msoControlButton)
.Caption = "MyMenu"
.OnAction = BuildProcArgString("MyProc", "A", "B", "C") 'You can add any number of arguments here!
End With

FUNCTION TO BUILD PROCEDURE ARGUMENTS (You just have to plop this in any of your modules)
Function BuildProcArgString(ByVal ProcName As String, ParamArray Args() As Variant)
For Each TempArg In Args
Temp = Temp + Chr(34) + TempArg + Chr(34) + ","
Next
BuildProcArgString = ProcName + "(" + Left(Temp, Len(Temp) - 1) + ")"
End Function

AND FINALLY - THE EXECUTABLE PROCEDURE!
Sub MyProc(x, y, z)
MsgBox x & y & z
End Sub

I'm sorry for "Self-Appraisal" but I guess this can't get any better. Neither you would ask for more.

I suppose Microsoft will include this in their future versions. :wink:
 
Upvote 0
You can pass argumentes with the .Parameter property, e.g.:

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

Old post for sure.
Regards
 
Upvote 0
Thanks for this post!

a simple button
this is my code and work great in 2007
Code: 
   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 ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
popup menu
Replies
3
Views
1K
KOKOSEK
KOKOSEK
M
  • Solved
"invalid procedure call or argument"
2
Replies
13
Views
585
Mr_Ragweed2
M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
397
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
G
  • Question
Passing arguments from a custom menu item
Replies
2
Views
605
RoryA
RoryA

Forum statistics

Threads
1,214,924
Messages
6,122,294
Members
449,077
Latest member
Rkmenon

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top