CODEWITHSUNDEEP
excelvba
user3470049
user3470049

Reputation: 447

How to call a macro from a button and pass arguments

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?

Upvotes: 34

Views: 100825

Answers (10)

t3chb0t
t3chb0t

Reputation: 18636

With Excel 16 (2024) I had to use the following pattern to make it work:

'<WorkbookName>.xlsm'!'MyFunction 1, "foo"'

where the macro was:

Sub MyFunction(ByVal Index as Integer, ByVal Name as String)
    ' ...
End Sub

Upvotes: 1

Pierre Mass&#233;
Pierre Mass&#233;

Reputation: 822

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.

Upvotes: 1

QA Collective
QA Collective

Reputation: 2419

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.

Upvotes: 46

AntiqueWhale
AntiqueWhale

Reputation: 181

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

Upvotes: 17

AjV Jsy
AjV Jsy

Reputation: 6075

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"'

Upvotes: 4

Michael
Michael

Reputation: 11

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"'.

Upvotes: 1

ffskitty
ffskitty

Reputation: 11

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?

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 3

314UnreadEmails
314UnreadEmails

Reputation: 311

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

Upvotes: 6

user3819867
user3819867

Reputation: 1120

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

Upvotes: 5

Related Questions