Determing range name that is tied to the active cell

[Guest]

Programmatically, I'd like to be able to determine the range name that is
tied to the active cell. How would I do that?

Thanks

 

[Don Guillett]

sub cellname()
msgbox active.cell.name
end sub

 

[Guest]

Sub demp2()
Dim n As Name
For Each n In ActiveWorkbook.Names
If Intersect(ActiveCell, Range(n.Name)) Is Nothing Then
Else
MsgBox (n.Name)
End If
Next
End Sub

 

[Norman Jones]

Hi Barbara,

Try:

'=============>>
Public Sub Tester()
Dim sStr As String

On Error Resume Next
sStr = Range("A1").Name.Name
If Err.Number = 0 Then
MsgBox sStr
End If
On Error GoTo 0

End Sub
'<<=============

 

[Guest]

Hi Norm:

What happens if there are several Named Ranges that include cell A1?

 

[Norman Jones]

Hi Gary,

What happens if there are several Named Ranges that include cell A1?

The first name (alphabetically) wpould be returned.

To return possible multiple names, it would be necessary to loop, as
suggested by you, and test that the name range and the cell/range were
identical.

 

[Don Guillett]

typo
msgbox activecell.name