Adding parameters to the OnAction property of Excel buttons

Adding parameters to the OnAction property of Excel buttons

Posted by Paul at 3:59 pm

Sometimes you need to pass parameters via a button in Excel but the standard OnAction property syntax does not allow this. If you try .OnAction=”myProc(99,’Some text’)” you will receive an error. But there is a way to get around this.

You can add a parameter to the OnAction property of the button. If you had a procedure like this:

Sub myProc(id As Integer, myText As String)
MsgBox (id & " " & myText)
End Sub

You can set the OnAction property of a button as follows:

sheet1.Shapes(1).OnAction = "'myProc 99,""Some text""'"

Careful with the quotes and double quotes, you need single quotes around the entire procedure call and parameters, you need repeated double quotes around strings. Separate parameters with commas.