Produce the name of the named range of activecell

[L. Howard Kittle]

Hi Excel users and expert,

Excel 2002 SP3.

I swear I have seen the answer to this but Google search did not get me
there.

I'm looking for code that does this:

If active cell is in named range Data1 then call Macro1
elseif activecell is in named range Data2 then call Macro2
End If

"activecell.name.name" did not work but I have to missing something. Gotta
be fairly simple.

I have 9 ranges and macros but I'm sure if I can get the syntax of the 2
above I can work it out.

Thanks,
Regards,
Howard

 

[Norman Jones]

Hi Howard,

One way:

'=============>>
Public Sub Tester001()

Dim rng1 As Range
Dim rng2 As Range

On Error Resume Next
Set rng1 = Intersect(ActiveCell, Range("Data1"))
Set rng2 = Intersect(ActiveCell, Range("Data2"))
On Error GoTo 0

If Not rng1 Is Nothing Then
Call Macro1
ElseIf Not rng2 Is Nothing Then
Macro2
Else
'do nothing
End If

End Sub
'<<=============

 

[L. Howard Kittle]

Thanks, Norm, exactly what I was looking for. Having seen your code, I am
now sure I have not seen something like that previously. But, looking it
over it all makes sense, but I would not have figured it out myself.

Thanks again, much appreciated.

Regards,
Howard