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?

You can actually type the macro name and argument enclosed in single quotes (eg `'macro 5'`) but don't use this method if your file is in .xlsb format. — Rory, Apr 26 '15 at 07:30

QA Collective · Answer 1 · 2019-03-08T22:41:48.827

44

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.

edited Mar 08 '19 at 22:41

answered Aug 06 '15 at 02:44

QA Collective

2,222
21
34

I have tried your approach, but I was not able to make it work. Seems that a function or macro is not allowed as an assigned macro if it receives arguments. – Aaron C May 02 '16 at 22:20
4

Enclosing the macro name is quotes worked for me: I set the macro name to `'Select 1,2'` for a button and it called my `Select` method correctly. – Quango May 26 '16 at 09:28
3

Thank you! This solution worked for passing a single argument - what if there are multiple arguments? – Abhi O. Sep 28 '16 at 17:14
For multiple arguments, repeat the 'Evaluate(...)' component: 'Button1_Click(Evaluate("Sheet1!$A$1"), Evaluate("Sheet1!$A$1"), ...)' – QA Collective Apr 16 '18 at 06:10
The parentheses [must be removed](https://stackoverflow.com/a/15040102/11683). `'Button1_Click "A string!", 7'` works, `'Button1_Click("A string!", 7)'` does not. – GSerg Jan 22 '19 at 16:17
Have you actually tried it in this exact context? Newer versions of office may have changed this and what you say makes sense in most VBA, but this case isn’t most VBA :) I think they may be using a different expression parser, because when I wrote this, they were absolutely required or it didn’t work. – QA Collective Jan 25 '19 at 05:46
How does this change for passing a range? – pavuxun Mar 08 '19 at 14:44
`Sheet1!$A$1` is a range. – QA Collective Mar 08 '19 at 22:44
Hum... `Evaluate` is a VBA function. You're executing a VBA function in the macro-assignment. Could *other* VBA functions be used there? – johny why Nov 21 '21 at 16:13
Wow, turns out you can use other VBA functions in the macro assignment. This works too: `'Button1_Click(Left("abc", 1))'` – johny why Nov 21 '21 at 16:20
Custom VBA functions work too, which could make this technique rather powerful. – johny why Nov 21 '21 at 16:26
You can also just pass the cell-address as text, and let VBA evaluate it. In that case, your VBA function should expect a String parameter. – johny why Nov 21 '21 at 16:30

score 16 · Answer 2 · edited Oct 18 '18 at 21:16

16

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

Macro

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.

Button

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

Type macro name

Then you see, problem solved.

Result

edited Oct 18 '18 at 21:16

JJJ

32,902
20
89
102

answered Oct 18 '18 at 20:50

AntiqueWhale

171
1
4

2

This absolutely should be the answer. The OP was looking for how to do it via the assign macro screen, not via code. – Mark Walsh Feb 19 '20 at 17:57
2

Also would like to point out if your arrow keys do not work to add the quotes, you can still use the mouse to move the cursor to the right location. – Mark Walsh Feb 19 '20 at 17:59

score 6 · Answer 3 · answered Apr 24 '15 at 22:25

6

Sub ert()
Call ert2(Cells(1,1).Value)
End Sub

answered Apr 24 '15 at 22:25

user3819867

1,114
1
8
18

score 6 · Answer 4 · answered Apr 24 '15 at 22:25

6

Use an activeX control command button and in the button click method, call the sub and pass the argument:

Private Sub CommandButton_Click()

  Dim x as Integer
  x = 1
  Call SomeSub(x)

End Sub

answered Apr 24 '15 at 22:25

314UnreadEmails

311
1
9

score 4 · Answer 5 · answered Apr 19 '18 at 10:42

To call this Sub from a button :

Public Sub TestButton(strMessage As String)
    MsgBox strMessage
End Sub

... be aware that the Sub won't be listed in the available macros, because it has a parameter. Just type in the call in single quotes : 'TestButton "Hello"'

score 3 · Answer 6 · edited May 23 '17 at 11:54

Called from a regular "forms" button on a worksheet you can do something like this:

Sub TestMe()
    Dim c, arr
    c = Application.Caller
    arr = Split(c, "_")
    If UBound(arr) > 0 Then MsgBox "Got " & arr(1)
End Sub

Where the calling button is named (eg) "Button_3"

Or (simpler) right-click the button and enter 'TestMe2 5' (Including the single-quotes)

Sub TestMe2(i)
    MsgBox "Got " & i
End Sub

See also: Excel 2010 - Error: Cannot run the macro SelectCell using .onAction

score 1 · Answer 7 · answered Mar 28 '18 at 22:40

I had trouble with my version of Personal.xlsb!'testForXXXX("Test Test")'. I got an error when clicking the button containing the Macro.

However, I was able to fix it by removing the "(" and ")". So, Personal.xlsb!'testForXXXX "Test Test"' worked (notice the space between testForXXXX and "Test...").

In fact, I didn't need Personal.xlsb! and was just able to use 'testForXXXX "Test Test"'.

score 0 · Answer 8 · edited Aug 26 '16 at 05:51

I hit the same issue with the assign button not being very useful until I realised that all the potential macros displayed were only the ones in my Personal.xlsb file that took no arguments. Then I typed Personal.xlsb!'macroNameWhichTakesArguments("arg1", ...)' and the sheet picked it up.

I.E. Personal.xlsb!'testForXXXX("Test Test")'

Where the macro testForXXXX take a string as input, then the sheet worked ok.

When you use the assign macro route for a button in excel 2007 at least testForXXXX will not show up in the list of potential macros if it takes args.

I suspect Aaron C got tripped up by this too. Maybe its best not to use a Personal.xlsb file?

score 0 · Answer 9 · answered Jun 12 '19 at 10:13

I would have liked to comment the answer by QA Collective but I do not have enough reputation point yet. Previous solutions did not work for me, as my macros are in named modules in my VBA project.

With Excel 2013, the way to pass a parameter to a macro using a button that worked in my context is :

'<module name>.<macro name> <parameter>'

e.g

'DataProcessor.disle "myText"'

or

'DataProcessor.editWSProcess True'

(boolean do not need double quotes when passed as parameters) Please note that single quotes are around the whole expression.

How to call a macro from a button and pass arguments

9 Answers9

Linked

Related