ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How to retreive the name of a named cell in an excel macro (https://www.excelbanter.com/excel-programming/415611-how-retreive-name-named-cell-excel-macro.html)

xavier

How to retreive the name of a named cell in an excel macro
 
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

How to retreive the name of a named cell in an excel macro
 
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


"Xavier" wrote:

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.


Tom Hutchins

How to retreive the name of a named cell in an excel macro
 
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

"Xavier" wrote:

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

How to retreive the name of a named cell in an excel macro
 
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


"Xavier" wrote:

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.


JP[_4_]

How to retreive the name of a named cell in an excel macro
 
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

On Aug 13, 12:35*pm, Xavier wrote:
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.




All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com