1

I'm having a little problem with range names. I have named the Range("A1") with the name myRange, which also appears in the name lists of the tab "Formulas / Names".

However, the following code snippet inserted into the "Change" event of the worksheet I'm working on is returning the reference rather than the name:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target.Name
End Sub

enter image description here

Can anyone explain me where I'm wrong? I'm rather expecting the MsgBox to show myRange, and not what's showing now. Thanks in advance.

Community
  • 1
  • 1
Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89

1 Answers1

2

Try something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nm As String
    On Error Resume Next
    nm = Target.Name.Name  'will error if Target<>whole named range
    On Error GoTo 0
    Debug.Print Target.Address(), nm
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • + 1 Sorry but have to close it as a duplicate. – Siddharth Rout Nov 06 '14 at 17:37
  • @SiddharthRout, you're right. For a very stupid mistake I had tried the solution Range.Name.Name and it didn't work, so I thought it was not my problem. Shall I delete this question or just leave it as duplicate (and eventually accepting Tim answer)? Don't know the right procedure in this case – Matteo NNZ Nov 06 '14 at 17:42
  • @MatteoNNZ: No don't delete it since Tim has already answered it. Not sure if you can accept the answer after the question has been marked duplicate. But if you can then I leave the decision with you :) – Siddharth Rout Nov 06 '14 at 17:44
  • @SiddharthRout yes I can, I just don't get the 2 points of reputations but it's correct like this, it's me not trying carefully the solution already existing, that I had found but tested incorrectly. But he answered correctly so I will mark his answer. Thanks ;) – Matteo NNZ Nov 06 '14 at 17:53
  • @MatteoNNZ: + 1 to your question (for above realization). Don't make that mistake again ;) Research saves not only your time but also of other experts who take time to answer. Not to mention that you get to self learn a lot :D – Siddharth Rout Nov 06 '14 at 18:02
  • @SiddharthRout I perfectly agree, I am myself avoiding asking before having taken full time to think and search, and trying always to reduce at minimum the effort of people who will have to answer. It was just a very stupid mistake in my code that did not make me pop-up the good result when I applied the forementioned method. But thank you hey :) – Matteo NNZ Nov 06 '14 at 18:12