Determine what Named Range the Target Address is and return Named Range Name

**jordan2322** · 06-18-2012, 09:47 PM

Hi Guys,

is there a function to return the name of a named range (if it exists) of the Target.Address?

Regards

**Leith Ross** · 06-18-2012, 09:50 PM

Hello jordan2322,

No there is not. But one could be written using VBA.

**jordan2322** · 06-18-2012, 09:59 PM

Something like this?
How would i modify this such that the target.address only needs to be within the named range - not match the named range address exactly.
As the named ranges are more than a single cell this (as written in the code below) would be impossible as the user can only double click a single cell not a range.
(note: I want this to be called using the double click event)


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    
    If Target.Address = Range("Fruit").Address Then
    MsgBox (Target.Address)
    End If
    
    End Sub

**mikerickson** · 06-18-2012, 10:23 PM

Try this

Dim oneName As Name
Dim foundName As Name

For Each oneName In ThisWorkbook.Names
    On Error Resume Next
    If oneName.RefersToRange Is Nothing Then
        On Error GoTo 0
        Rem name is not range
    Else
        On Error GoTo 0
        If oneName.RefersToRange.Parent.Name = Target.Parent.Name Then
            If Not Application.Intersect(Target, oneName.RefersToRange) Is Nothing Then
                Set foundName = oneName
            End If
        End If
    End If
Next oneName

If foundName Is Nothing Then
    MsgBox "not inside a named range"
Else
    MsgBox "target is inside named range, " & foundName.Name
End If

**jordan2322** · 06-18-2012, 10:29 PM


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim nName As Name
    For Each nName In ThisWorkbook.Names

            If Not Intersect(Range(nName.Name), Target) Is Nothing Then
                Cancel = True
                MsgBox (nName.Name)
                Exit For
            End If

    Next nName
End Sub

Solved using the above. Thank you Mikerickson for your reply!

**MarvinP** · 06-18-2012, 10:49 PM

What happens if the cell you click on is in more than one named range? Do you want all named ranges that contain the double clicked cell to appear?

Determine what Named Range the Target Address is and return Named Range Name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Determine what Named Range the Target Address is and return Named Range Name

Re: Determine what Named Range the Target Address is and return Named Range Name

Re: Determine what Named Range the Target Address is and return Named Range Name

Re: Determine what Named Range the Target Address is and return Named Range Name

Re: Determine what Named Range the Target Address is and return Named Range Name

Re: Determine what Named Range the Target Address is and return Named Range Name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions