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

Hi,
same question innit??

From Chip Pearsons site....
http://www.cpearson.com/excel/excelM.htm

Public Function CellInNamedRange(Rng As Range) As String

Dim N As Name
Dim C As Range
Dim TestRng As Range
On Error Resume Next

For Each N In ActiveWorkbook.Names
Set C = Nothing
Set TestRng = N.RefersToRange
Set C = Application.Intersect(TestRng, Rng)
If Not C Is Nothing Then
CellInNamedRange = N.Name
Exit Function
End If
Next N
CellInNamedRange = ""

End Function


Public Sub SelectRange()

Dim RngName As String
Dim R As Range
Set R = ActiveCell
Dim Msg As String

Msg = "Active Cell Is Not In A Named Range."
RngName = CellInNamedRange(R)
If RngName < "" Then
Range(RngName).Select
Msg = "Range: " + RngName + " Selected."
End If

Application.StatusBar = Msg

End Sub


Hth,
OJ

Tetsuya

One way:

Sub GetName()
'Leo Heuser April 8, 2005
Dim Nam As Name

For Each Nam In ActiveWorkbook.Names
If Not Intersect(ActiveCell, Range(Nam.RefersTo)) Is Nothing Then
MsgBox Nam.Name
End If
Next Nam

End Sub


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Tetsuya Oguma" <Tetsuya skrev i en
meddelelse ...
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

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

Leo,

your code will produce unhandled errors if book contains names with
either constants or formulas

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


Leo Heuser wrote :

Tetsuya

One way:

Sub GetName()
'Leo Heuser April 8, 2005
Dim Nam As Name

For Each Nam In ActiveWorkbook.Names
If Not Intersect(ActiveCell, Range(Nam.RefersTo)) Is Nothing
Then MsgBox Nam.Name
End If
Next Nam

End Sub

You're right. I "forgot" a line with
On Error Resume Next

Thanks!

LeoH


"keepITcool" skrev i en meddelelse
.com...
Leo,

your code will produce unhandled errors if book contains names with
either constants or formulas

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


Leo Heuser wrote :

Tetsuya

One way:

Sub GetName()
'Leo Heuser April 8, 2005
Dim Nam As Name

For Each Nam In ActiveWorkbook.Names
If Not Intersect(ActiveCell, Range(Nam.RefersTo)) Is Nothing
Then MsgBox Nam.Name
End If
Next Nam

End Sub