excel - How to call a macro from a button and pass arguments

Question

I want to add a button to my excel worksheet which should call a macro that can handle one agument (an integer value). Sadly when creating the button, I cannot link any macro that has arguments. Also just typing the macro and the argument does not work.

Is there any simple solution to pass an argument to a macro when a button is pressed?

Answer 1

Yes, you can assign a macro to a button (or other excel controls/menu actions) and pass constant OR variable arguments to it.

In the 'Assign Macro' window (right-click on object and select 'Assign Macro'):

• Enclose the macro name in single quotes e.g. to pass 2 constants: 'Button1_Click("A string!", 7)'
• Select 'This Workbook' for the 'Macros in' field
• If you wish to pass a variable (like the value of a cell), enclose the parameter in Evaluate()

For example, to pass the value of Sheet1!$A$1 to a button function, you would have the following text in the 'Macro name:' field:

Button1_Click(Evaluate("Sheet1!$A$1"))

If you don't enclose your variable argument with an 'Evaluate' function, excel returns the error 'Formula is too complex to be assigned to an object.'.

I would have included an image if this were allowed on my first post.