Get Name of Selected Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks, that was what I figured.

Bernie Deitrick said:
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
Click to expand...
 
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
 
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
 
Charlie said:
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
Click to expand...
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
 
Back
Top