Button macro with parameter

[Tanquen]

Not sure why this is so odd in Excel.

I did find that if your VBA sub has a parameter it will not be in the list when you right click and then try and assign macro. Seems odd but ok.

I found info saying you just have to manually type it in. That worked but you can not use any arrows or anything to move the curser.

I had it working last Friday but today without having changed anything I get an error that the sub is either no longer in the Excel file or macros are disabled. It is there and other subs without parameters still work.

Why you do this to me MS?

 

[Tanquen]

There is the workaround of creating a sub for every value that you may want to pass and then assigning each of those to a button.
Each sub is hardcoded with that one value that you can assign to one button.

It works but I wish the other way like this, kept working.
'VBA2.xlsm'!'CopyAndConcatenateValuesToSheet "GEN"'

 

[Jaafar Tribak]

How are you supposed to pass the parameter value when clicking the button ?!

 

[Tanquen]

I found info saying you just have to manually type it in. That worked but you can not use any arrows or anything to move the curser.

It works but I wish the other way like this, kept working.
'VBA2.xlsm'!'CopyAndConcatenateValuesToSheet "GEN"'

You can go to the Assign Macro popup from the button and as the sub with parameters is not listed you can type it in with parameters.

Something like this:
'NameOfMacro "variable1", "variable2"'

Or this:
'VBA2.xlsm'!'CopyAndConcatenateValuesToSheet "GEN"'

This worked until I closed down the workbook for the weekend and tried to use it again. Seems like there is a security check that happens when the workbook is opened.

More info here:
Assign macro with arguments to a Form Control button

 

[Jaafar Tribak]

I never knew about this.
Unless I am missing something, in addition to the fact that the typed macro and variables disappear after closing the Macros dialog, I don't see any benefit as you still need to hardcode the variable values.

 

[Tanquen]

Not a huge deal. I just think it's easier to add the parameters to the button. I don't know why they made it so clunky and then broke it. It seems more intuitive if you have a sub that requires parameters to let you... assign the sub to the button along with parameters. Also, when it worked I didn't have to have a bunch of subs in my script for each button and I didn't have to wade through a laundry list of more subs when assigning macros.

 

[Jaafar Tribak]

Are you referring to buttons like on your worksheet?
What I don't understand is how you will be passing non-hardcoded arguments when clicking the button.

Can you show us some small code example of what you are trying to do when the button is clicked?

 

[Tanquen]

Yes, buttons on the worksheet.

"how you will be passing non-hardcoded arguments"
Not sure this was a question. I have to give the sub a value somewhere, that will only be passed by one button? I don't have a way to auto generate the subs parameter value. Maybe you can get the value to pass from the button text string but then it is still "hardcoded".

I already did give examples. Just wanting to run the same sub when the button is clicked but pass a different parameter value.
Did you look at the "Assign macro with arguments to a Form Control button" I added a link to? It covers what did work when added to the open workbook but stopped after opening it again a few days latter.

This is one that worked for a time. I had like seven buttons and will be adding more. All running the same sub "CopyAndConcatenateValuesToSheet" and just passing a different value.
I'd copy one button and then change the value passed on the Assign Macro popup by including it in the 'Macro name' string . This one would pass "GEN" as the parameter value.
'VBA2.xlsm'!'CopyAndConcatenateValuesToSheet "GEN"'

I felt that was a lot easier than going back into the script, adding a new sub for something like "PDU", then going back to the sheet, adding a new button called "PDU", then going to the Assign Macro popup and looking through the growing list of subs for each button and linking the button to the new "PDU" sub.

While it was working, I could just copy a button on the sheet, open the Assign Macro popup and change "GEN" to "PDU" in the 'Macro name' and not have to make more subs in the script. They do not disappear so you can just change the parameter value.
'VBA2.xlsm'!'CopyAndConcatenateValuesToSheet "PDU"'

 

[Eric W]

You could write a preliminary sub that calls your CopyAndConcatenateValuesToSheet module. Something like this:

Sub PreCACVTS()

    Select Case Application.Caller
        Case "Button 1"
            CopyAndConcatenateValuesToSheet "PDU"
        Case "Button 3"
            CopyAndConcatenateValuesToSheet "GEN"
        Case Else
            MsgBox "Unrecognized button"
    End Select
    
End Sub

Sub CopyAndConcatenateValuesToSheet(MyParm)

    MsgBox MyParm
    
End Sub

These have to be Form Buttons. But whenever you add a new button, just set its macro to PreCACVTS, then add a line to the Select Case with the button name and the parameter you want to pass.