I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). Even if I have to use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!

Two options:

Private Sub CommandButton1_Click()
Call macro1(Range("NamedRange1"))
End Sub

or

Private Sub CommandButton2_Click()
Dim myRng As Range
Set myRng = Range("NamedRange2")
Call macro1(myRng)
End Sub

Regards,
Per

On 1 Apr., 21:46, hjc wrote:
I have written a macro that I would like to have operate the same way on
different data ranges. *I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
* * Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
* * Call Macro1( "NamedRange2" )
End Sub

and so on. *However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). *Even if I have to use
cell references instead of a named range, I could live with that. *Does
anybody know if there is a way to do this?

Thanks!

The one thing you didn't show us that we needed to see is your macro
(actually, since it receives an argument, it is a subroutine and not a
macro). See if this minimal structured relationship helps you any...

Private Sub CommandButton2_Click()
Call Macro1("NamedRange2")
End Sub

Sub Macro1(RngName As String)
MsgBox Range(RngName).Address
End Sub

--
Rick (MVP - Excel)



"hjc" wrote in message
...
I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that
each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying
a
range name in the call to the macro (if there is one). Even if I have to
use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!

Same macro to work on different ranges?

Private Sub CommandButton1_Click()
Application.Goto Reference:="NamedRange1"
Call Macro1
End Sub

Private Sub CommandButton1_Click()
Application.Goto Reference:="NamedRange2"
Call Macro1
End Sub


Gord Dibben MS Excel MVP

On Thu, 1 Apr 2010 12:46:05 -0700, hjc
wrote:

I have written a macro that I would like to have operate the same way on
different data ranges. I tried creating a generic macro that accepted a
range of data as an input parameter, then I created several buttons that each
called the same macro, but with a different argument, as follows:

Private Sub CommandButton1_Click()
Call Macro1( "NamedRange1" )
End Sub

Private Sub CommandButton2_Click()
Call Macro1( "NamedRange2" )
End Sub

and so on. However, I can't seem to find the right syntax for specifying a
range name in the call to the macro (if there is one). Even if I have to use
cell references instead of a named range, I could live with that. Does
anybody know if there is a way to do this?

Thanks!