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

 

[Alex Blakenburg]

I don't know what that is. Do you have a link to a good tutorial?

You only need a very basic drop down list for this. Here is a reference, it should take you straight the validation list section.
The video just below it is < 2 mins.
(Contextures)
Excel Drop Down Lists - Data Validation

 

[JenniferMurphy]

Would it be possible to select the range (or if the columns are fixed, simply any cell on the row) before clicking the button? If so, you could use one macro that uses Selection (if you are selecting the entire range) or the range constructed from the ActiveCell (if the columns are fixed). Alternately, if your columns are fixed, you could designate a cell to type the row number into and create the range inside the macro using that.

Perfect. Now I'm kicking myself for not thinking of it myself.

Here's a little test code I tossed together. And the surprising thing is that it works.

'========================================================================
'                 Add Up the Numbers in the Selection
'========================================================================
Sub SelectionTest()

Dim Weights As Range
Set Weights = Selection

Dim NumRows As Long
Dim NumCols As Long
NumRows = Weights.Rows.Count
NumCols = Weights.Columns.Count
Dim i As Long
Dim j As Long
Dim Sum As Double
Sum = 0

For i = 1 To NumRows
  For j = 1 To NumCols
    Sum = Sum + Weights(i, j)
  Next j
Next i
 
MsgBox Sum
 
End Sub

Thank you! Thank You! Thank you! ???

 

[JenniferMurphy]

You only need a very basic drop down list for this. Here is a reference, it should take you straight the validation list section.
The video just below it is < 2 mins.
(Contextures)
Excel Drop Down Lists - Data Validation

Thanks. Rick gave me a simple solution that I already understand, so I'm going to go with it so I can get this pesky project done. When I get a minute, I'll definitely check out these lists. Thanks. I appreciate it.

 

[Alex Blakenburg]

Thanks. Rick gave me a simple solution that I already understand, so I'm going to go with it so I can get this pesky project done. When I get a minute, I'll definitely check out these lists. Thanks. I appreciate it.

No problem, I appreciate you coming back to me.