Facebook
TwitterSometimes 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 Subyou 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.
Tagged: Excel, OnAction, VBA
Hi Paul,
Could it be that there is a limit in the length of the string assigned to the onAction property?
Christoph
I don’t believe so, as in my case the required function name and parameters were short. I think it just didn’t support the original syntax.
Thanks!
one remark,- “myProc” must live in Modules, not in sheets
Thank you very much. It really works! I’ve been looking for solution for few hours.