How to obtain Cell Name
 

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!!!

How to obtain Cell Name
 

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



"Nicholas!" wrote in message
...
| 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!!!

How to obtain Cell Name
 

Hi Nicholas,

Try:

Target.Value = ActiveCell.Name.Name


---
Regards,
Norman



"Nicholas!" wrote in message
...
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!!!

How to obtain Cell Name
 

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

"William" wrote in message
...
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



"Nicholas!" wrote in message
...
| 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!!!