VBA Pass arguments with .onAction
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* :-)
You need to add a space after your Sub name, like this:
.OnAction = "'InsertRowWithContent " & C & "'"
^^^^^^^^^^
EDIT
Also, since you're passing a Long parameter, you shouldn't enclose it in quotes.
EDIT 2
OK, this will require some special sauce (code). I've been experimenting and I got the following to work.
In the Sheet1 Excel object:
Option Explicit
Sub DestroyToolbar()
Application.CommandBars("DoomBar").Delete
End Sub
Sub MakeToolbar()
Dim C As Long
C = 100
With Application
.CommandBars.Add(Name:="DoomBar").Visible = True
With .CommandBars("DoomBar")
.Controls.Add Type:=msoControlButton, ID:=2950, Before:=1
With .Controls(1)
.OnAction = "'PressMe " & C & "'"
End With
End With
End With
End Sub
In a new standard code module, add this:
Public Sub PressMe(C As Long)
With Application.CommandBars("DoomBar")
With .Controls(1)
MsgBox "The value of C that was passed to this macro is: " & C
End With
End With
End Sub
If you run MakeToolbar
, it will create a new toolbar in the "Add-ins" ribbon. To remove it, you can run DestroyToolbar
.
Once the toolbar is in place, then clicking the button should display a messagebox with the value of C
(in my example, 100).
I've tested the above in Excel 2010 and it worked, in a brand new .xlsm file.