How to call a sub from a sheet with arguments?

[JenniferMurphy]

I have a Sub that does a lot of analysis on one named range and returns the results to another named range. I call it using a button control. The problem is if I want to have it work on more than 1 range, I have to keep renaming the ranges. I would like to be able to have a different button control for each range, on the same sheet or a different one in the same book. Each button would call the same Sub, but with a different range.

I tried adding parameters to the Sub statement (Sub MySub(Name1 as range)), but then it no longer appears in the list of macros to assign to the button.

Is there a way to do this?

Thanks

 

[GWteB]

Create parameterless macros:

Public Sub InvokeOne()
    Dim One As Range
    mySub One
End Sub

Public Sub InvokeTwo()
    Dim Two As Range
    mySub Two
End Sub

 

[Logit]

You could use Input Boxes to ask which sheetthen another Input Box to ask which range ...

 

[Alex Blakenburg]

Following on from @GWteB's comment. Paul Kelly from Excel Macro Mastery normally has a module called Click_Events and he puts all his initiating macros there.
eg
Module > Click_Events

Contains all the Click Macros eg Public Sub GenerateReports_Click()​

and all that macro does is​

Call MySub (rng) - the macro that actually does the work.​

 

[JenniferMurphy]

Create parameterless macros:

Public Sub InvokeOne()
    Dim One As Range
    mySub One
End Sub

Public Sub InvokeTwo()
    Dim Two As Range
    mySub Two
End Sub

I thought of that, but that would be a bit inconvenient if I needed more than a few different calls. I was looking for a more general solution.

 

[JenniferMurphy]

You could use Input Boxes to ask which sheetthen another Input Box to ask which range ...

That's an interesting option that hadn't occurred to me. Maybe I'll play with that a bit.

 

[GWteB]

I thought of that, but that would be a bit inconvenient if I needed more than a few different calls. I was looking for a more general solution.

This is as general it can get.

 

[JenniferMurphy]

Following on from @GWteB's comment. Paul Kelly from Excel Macro Mastery normally has a module called Click_Events and he puts all his initiating macros there.
eg
Module > Click_Events

Contains all the Click Macros eg Public Sub GenerateReports_Click()​

and all that macro does is​

Call MySub (rng) - the macro that actually does the work.​

Is that here:

Paul Kelly

 

[GWteB]

Yep, Paul has very nice insights.

 

[JenniferMurphy]

I thought about calling the Sub from a UDF. I'd pass the arguments to the UDF and it would pass them to the Sub. But the UDF got an error message (I can't recall which).