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.

Answer 2

Suppose you have a public sub take 1 argument like below (just for explanation purposes.)

And you insert a button on Worksheet like below, and you can not find the macro name when you want to assign your sub to this button.

Now, you can type in your sub name + space + argument manually in single quotes, like below, click ok.

Then you see, problem solved.