Get Name of Selected Range

[Guest]

Good Morning,

Is there a method or property that returns the Name (if any) of a selected
or specific range? Something like:

NameOfRange = ThisWorkbook.Names.Item(Selection).Name
NameOfRange = ThisWorkbook.Names.Item(ActiveCell).Name
NameOfRange = ThisWorkbook.Names.Item(Cells(1, 1)).Name
NameOfRange = ThisWorkbook.Names.Item(Range("A1")).Name
NameOfRange = ThisWorkbook.Names.Item(Range("A1:B2")).Name

TIA
Charlie

 

[Bernie Deitrick]

Charlie,

No. You need to iterate through the names collection:

Dim myname As Name
For Each myname In ActiveWorkbook.Names
If Selection.Address = Range(myname).Address Then
MsgBox "The selection is named " & myname.Name
End If
Next

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, that was what I figured.

Charlie,

No. You need to iterate through the names collection:

Dim myname As Name
For Each myname In ActiveWorkbook.Names
If Selection.Address = Range(myname).Address Then
MsgBox "The selection is named " & myname.Name
End If
Next

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

try

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

 

[Bernie Deitrick]

Charlie,

I got sloppy, and forgot about needing to check the named range's parent sheet.

You should use something like this one:

Sub TryNow()
Dim myname As Name
For Each myname In ActiveWorkbook.Names
If Selection.Address(, , , True) = Range(myname).Address(, , , True) Then
MsgBox "The selection is named " & myname.Name
Exit Sub
End If
Next
MsgBox "The selection is not a named range"
End Sub

HTH,
Bernie
MS Excel MVP

 

[Alan Beban]

Good Morning,

Is there a method or property that returns the Name (if any) of a selected
or specific range? Something like:

NameOfRange = ThisWorkbook.Names.Item(Selection).Name
NameOfRange = ThisWorkbook.Names.Item(ActiveCell).Name
NameOfRange = ThisWorkbook.Names.Item(Cells(1, 1)).Name
NameOfRange = ThisWorkbook.Names.Item(Range("A1")).Name
NameOfRange = ThisWorkbook.Names.Item(Range("A1:B2")).Name

TIA
Charlie

E.g.:

Sub testIt3a()
On Error Resume Next
MsgBox Selection.Name.Name
If Err <> 0 Then MsgBox "The specified range is not a named range"
End Sub

Alan Beban