Prev: Save a date as a variable and run/convert formula against the vari
Next: Hovering over an object
From: hjc on 1 Apr 2010 15:46
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!
From: Per Jessen on 1 Apr 2010 15:54
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 <h...(a)discussions.microsoft.com> 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!

From: Rick Rothstein on 1 Apr 2010 15:54
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" <hjc(a)discussions.microsoft.com> wrote in message
news:0D55EAB7-0068-43E2-8191-841C2D534C30(a)microsoft.com...
> 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!

From: Gord Dibben on 1 Apr 2010 15:59
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 <hjc(a)discussions.microsoft.com>
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!

 | 
Pages: 1
Prev: Save a date as a variable and run/convert formula against the vari
Next: Hovering over an object