[SOLVED] VBA Pass arguments with .onAction

VBA Pass arguments with .onAction

this is how my sub looks like:

Sub InsertRowWithContent(rowNo As Long)

This is my .onAction:

.OnAction = "'InsertRowWithContent""" & C & """'"

C is a Long variable declared earlier.

It says macro not found. It worked fine before adding an argument!

Solution

I have sucessfully passed arguments with this syntax:

.OnAction = "=InsertRowWithContent(" & C & ")"

Considerations:

C is a long. So don't add quotas, just as you wouldn't if you would call the Sub in the code.
OnAction evaluates an expression. Therefore according to my experience, the expression needs an equal sign, and as far as I now, it must be a function. Only automated local callbacks are subs.

EDIT

My answer above has Access as background. Djikay's answer works fine with Excel. Yet after some digging, I am quiet sure that simply Word doesn't understand either of those syntaxes. Word VBA cannot pass a parameter to a sub in an OnAction statement. At least for the moment it's best to accept this.

But here is what definitively runs with Word (2010):

Create your command bar and the button. For the OnAction, only pass the name of the Sub. But use the button's Parameter property.

' Add bar
Set cdb = Application.CommandBars.Add("SomeTest", , False, False)
cdb.Visible = True

' Add button
Set cbb = cdb.Controls.Add
cbb.Caption = "PressMe"
cbb.OnAction = "TheCallback"
cbb.Parameter = 456

Then, access the parameter by the CommandBars.ActionControl.Parameter expression:

Public Sub TheCallback()

  MsgBox "The parameter passed is: " & CommandBars.ActionControl.Parameter

End Sub

ActionControl is very similar (if not the same) as ActiveControl under Access: it is the control that was clicked last.

Source: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24982922.html

*phuu* :-)