How to obtain Cell Name

[Nicholas!]

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

[email protected]

"Nicholas!" <[email protected]> wrote in message
news:[email protected]...
| 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


---
Regards,
Norman



"Nicholas!" <[email protected]> wrote in message
news:[email protected]...
> 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!!!

[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

"William" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> [email protected]
>
> "Nicholas!" <[email protected]> wrote in message
> news:[email protected]...
> | 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!!!
>
>