How to retreive the name of a named cell in an excel macro

[Xavier]

I have no problem in addressing cells with name, adding names to the list but
have not yet found the way to retreive the name of the active cells. If any
one has a clue, this would be very appriciated.

 

[JLGWhiz]

A named cell is a range object with a string name, so:

Sub Nms()
Range("A1").Name = "NamedCell"
Range("NamedCell").Value = Range("A1").Value
MsgBox Range("NamedCell").Address
End Sub

 

[Tom Hutchins]

It's awkward because there is no Names collection for the Range object. Also,
a range can have more than one name assigned to it. The following code
displays every name assigned to the active cell only:

Sub AAAAA()
Dim x As Long
For x = 1 To ActiveWorkbook.Names.Count
If Range(ActiveWorkbook.Names(x)).Address = _
ActiveCell.Address Then
MsgBox ActiveWorkbook.Names(x).Name
End If
Next x
End Sub

Hope this helps,

Hutch

 

[JLGWhiz]

Sorry, I missed the key question. Here, look at Nm:

Sub Nms()
Range("A1").Name = "NamedCell"
Range("NamedCell").Value = Range("A1").Value
MsgBox Range("NamedCell").Address
Nm = ActiveCell.Name.Name
MsgBox Nm
End Sub

 

[JP]

If I name cell E2 "MyCell", the following code returns the name:

Range("E2").Name.name

This only works if it is a single cell named range.

--JP