• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Excel VBA - find out if activecell is Named Range

For a Worksheet_BeforeDoubleClick event I want to find out if the Target is a Named Range "ClickCell". I tried the Name property which gives Sheet1!$D$4. I want it to return "ClickCell".
Thanks.
0
hindersaliva
Asked:
hindersaliva
  • 2
1 Solution
 
SiddharthRoutCommented:
Is this what you want?

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

Function CheckRange(TargetToCheck As Range, NMToCheck As Range) As Boolean
    On Error Resume Next
    CheckRange = Not Application.Intersect(TargetToCheck, NMToCheck) Is Nothing
    Err.Clear: On Error GoTo -1: On Error GoTo 0
End Function

Open in new window


Sid
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
You can of course do it as a one liner using:


intersect(range("ClickCell"), target) is nothing

Chris
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Intersect(Range("ClickCell"), Target) Is Nothing Then
        MsgBox "Not in range"
    Else
        MsgBox "Is in range"
    End If

End Sub

Open in new window

0
 
hindersalivaAuthor Commented:
Perfect. Thanks Chris.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Glad it helped, and thanks for the prompt response.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now