and yet another (more elaborate?) way:

:Uses NameLocal
:Gets dynamic ranges too...


Option Explicit


Function GetRangeName(oRange As Range, Optional bExpand As Boolean) As
String
Dim rg As Range, nm As Name

On Error Resume Next
Set nm = oRange.Name
If Not nm Is Nothing Then
GetRangeName = nm.NameLocal
End If
If bExpand Then
'part of larger named range?
'(also dynamic names with index of offset formulas)
For Each nm In oRange.Parent.Parent.Names
Set rg = Range(nm.Name)
If Not rg Is Nothing Then
If Not Intersect(oRange, rg) Is Nothing Then
GetRangeName = GetRangeName & ";" & nm.NameLocal
End If
End If
Next
If Left$(GetRangeName, 1) = ";" Then
GetRangeName = Mid$(GetRangeName, 2)
End If
End If

End Function

Sub Demo()
MsgBox "Activecell (not expanded)" & vbTab & _
GetRangeName(ActiveCell, False) & vbLf & _
"Activecell (expanded) " & vbTab & _
GetRangeName(ActiveCell, True) & vbLf & _
"Selection (not expanded)" & vbTab & _
GetRangeName(ActiveWindow.RangeSelection, False) & vbLf & _
"Selection (expanded) " & vbTab & _
GetRangeName(ActiveWindow.RangeSelection, True)
End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tetsuya Oguma wrote :

All,

Lots of people want to know if activecell is part of named range.

My question is a bit different; How to get name of named range where
activecell is.

Looking through Range object, but, mmmm, cannot find any.

Can anyone know how to do this?
---
Tetsuya, Singapore