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)
Thanks,
Chas
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)
Code:
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