• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • 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 BottomleyCommented:
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 BottomleyCommented:
Glad it helped, and thanks for the prompt response.

Chris
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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