vba Return Name of range selected cell in in

[Chas17]

I have 12 named ranges on a sheet, ArrM1 thru ArrM12.
Each named range is same size, 6 Rows by 7 columns.

Q. If user selects a cell on sheet that is in one of these ranges, what is code to return name of range?
e.g. ArrM1 is cells c10:i15, user selects cell d12; I would like vba to return the name of the range "ArrM1".

Reason, I have the code below so if user selects cell in ArrM1 code is executed, but rather than reproduce code 12 times for each range I thought I could first see where user selects and then change variable for vrange,
Set vrange = wksYearlyCalendar.Range("ArrM1") to
Set vrange = wksYearlyCalendar.Range("ArrM" & x)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CalDaySel As String, CalDateSel As String
Dim vrange As Range
Dim cell As Range

Set vrange = wksYearlyCalendar.Range("ArrM1")

For Each cell In Target
    If Union(cell, vrange).Address = vrange.Address Then
        If ActiveCell = "" Then
            MsgBox "Please Select a valid date", , "Blank Day Selected"
            Exit Sub
        End If
  
        CalDaySel = ActiveCell
        CalDateSel = Application.WorksheetFunction.Index(wksFormulas.Range("startDates"), wksYearlyCalendar.Range("ArrNum1")) + CalDaySel - 1

        wksYearlyCalendar.Range("CalSelDayDate") = CalDateSel
        wksYearlyCalendar.Range("chascalWeekSelNote") = CalDateSel & " is in Week "

    End If
Next cell

End Sub

Thanks,
Chas

 

[mikerickson]

This will account for named ranges that are on different sheets and names that are not ranges.

Dim rangeOfInterest As Range
Dim oneName As Name
Set rangeOfInterest = Range("B2:B3")

For Each oneName In ThisWorkbook.Names
    On Error Resume Next
    If Application.Intersect(oneName.RefersToRange, rangeOfInterest) Is Nothing Then
        Rem not in named Range
    Else
        MsgBox rangeOfInterest.Address & " is in the named range " & oneName.Name
        Exit For
    End If
Next oneName

 

[baldmosher]

I used:

Range("A1").CurrentRegion.Name.name

to get the Named Range that cell A1 is in. Note that if the cell is not within a named range you'll get an 1004 error.

This is intriguing, it sort of works, I managed to get a selection of rows to report back the range name using Selection.Name.Name (i.e. same logic) but when I tried it on a different range of two adjacent cells it didn't work (1004 error).

I wonder if a 1004 error is also returned if there is more than one range name covering the selected cell(s).

Can it be made more specific I wonder, to only look for the range name that applies to the whole selection?


The code in the post above mine works well enough though.....

 

[mikerickson]

..Can it be made more specific I wonder, to only look for the range name that applies to the whole selection? ..

Have you tried

MsgBox Selection.Name.Name

 

[baldmosher]

Have you tried

MsgBox Selection.Name.Name

Yeah that's exactly what I tried, but it's returning 1004 errors sometimes, but not always. Very strange. Maybe cos the range is referring to merged cells, maybe cos the cells are part of a number of ranges.... Could be any number of things but your code routine above does work perfectly well so I've used that and I'm not going to spend time trying to make the other work. The workbook I'm using has an unnecessarily complicated system of range names which doesn't always help

 

[mikerickson]

My guess is that its the Merged Cells. Try eliminating them. Horizontal Alignment - Center Across Selection is much more robust than merging cells.

 

[jwolter]

One other note about some of the code that has been posted on this thread. By testing if the intersection of two ranges is not nothing, these functions only establish that there is an intersection between the range of interest and the named range. For a single-celled range this is not a problem, but if your range is more than one cell, you could find a named range that does not completely contain the range of interest.

Also, these functions assume there is only one named range that refers to the range of interest. If there are two, things get more complicated.

 

[pgc01]

Hi

Try:

Function GetNamedRange(r As Range) As String
Dim j As Long
 
For j = 1 To 12
    If Not Intersect(ActiveWorkbook.Names("ArrM" & j).RefersToRange, r) Is Nothing Then
        GetNamedRange = "ArrM" & j
        Exit Function
    End If
Next j
End Function

For ex., test with the active cell:

Sub test()
MsgBox GetNamedRange(ActiveCell)
End Sub