How to obtain Cell Name

[Guest]

Hi,

My question is the following.

After naming a range (in this case, a single cell) in a Worksheet, I need to
obtain the name of the ActiveCell after doing a "double-click". I have no
problem with the "general" programing for making an event happen in the
active cell with the double click as a trigger, but instead of getting the
name, I get the reference.

The code I use:

ActiveCell.Name

I get, for example: Sheet1!C4R1, instead of the cell name, that's what I need.

If anyone could give me a hand with this will be of great help.

Many thanks.

Rgds!!!

 

[William]

Hi

Doubtless there are better solutions but you can try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim nm As Name
For Each nm In ThisWorkbook.Names
If Range(nm).Address = Target.Address Then MsgBox nm.Name
Next nm
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Hi,
|
| My question is the following.
|
| After naming a range (in this case, a single cell) in a Worksheet, I need
to
| obtain the name of the ActiveCell after doing a "double-click". I have no
| problem with the "general" programing for making an event happen in the
| active cell with the double click as a trigger, but instead of getting the
| name, I get the reference.
|
| The code I use:
|
| ActiveCell.Name
|
| I get, for example: Sheet1!C4R1, instead of the cell name, that's what I
need.
|
| If anyone could give me a hand with this will be of great help.
|
| Many thanks.
|
| Rgds!!!

 

[Norman Jones]

Hi Nicholas,

Try:

Target.Value = ActiveCell.Name.Name

 

[Robin Hammond]

Building on William's answer, you could get incorrect results if there are
ranges in several sheets referring to the same address. I am sure this can
be improved upon, but this appears to pick up the difference and handle
sheet specific names such as Sheet1!MyRange.

Sub Test()
Dim strName As String
strName = GetRangeName(ActiveCell)
If strName <> "" Then
MsgBox strName
End If
End Sub

Public Function GetRangeName(rngTest As Range) As String
Dim nm As Name
For Each nm In ThisWorkbook.Names
If nm.RefersTo = rngTest.Name Then
GetRangeName = nm.Name
Exit Function
End If
Next nm
End Function

Robin Hammond
www.enhanceddatasystems.com