I have quite a few defined range names in an active worksheet. Can I
get the defined NAME of of the range that the activecell resides ( In
VBA of course).

Adapted from a post by Tom Ogilvy:

Sub nm()
Dim nm As Name, rng As Range
For Each nm In ThisWorkbook.Names
On Error Resume Next
Set rng = nm.RefersToRange
On Error GoTo 0
If Not rng Is Nothing Then
If rng.Parent.Name = ActiveSheet.Name Then
If Not Intersect(rng, ActiveCell) Is Nothing Then
MsgBox "Member of " & nm.Name & ", " & _
rng.Address(external:=True)
' either use "exit for" or continue to loop to see
' if it is in more than one named range
End If
End If
End If
Next

End Sub

Hope this helps
Rowan

"Arishy" wrote:

I have quite a few defined range names in an active worksheet. Can I
get the defined NAME of of the range that the activecell resides ( In
VBA of course).

Thank you Rowan...and of course Our very dear Tom...
It worked like a charm.

*** Sent via Developersdex http://www.developersdex.com ***

You're welcome.

"Samir Arishy" wrote:



Thank you Rowan...and of course Our very dear Tom...
It worked like a charm.

*** Sent via Developersdex http://www.developersdex.com ***