Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect if a selected cell is within a named range
If I have a worksheet with a number of different (non-overlapping)
custom named ares (i.e. part of the WorkSheet.Names collection), is there any simple way I can detect the region that a selected cell might be part of? I could get the row and column index of the cell in question and then loop through all the Name objects in the collection, testing if it falls within the R and C range, but I was hoping there might be something simpler. Thanks in advance Craig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect if a selected cell is within a named range
Dim nme As Name
For Each nme In ActiveWorkbook.Names If Not Intersect(ActiveCell, Range(nme.Name)) Is Nothing Then MsgBox nme.Name End If On Error GoTo 0 Next nme -- HTH RP (remove nothere from the email address if mailing direct) "CraigB" wrote in message om... If I have a worksheet with a number of different (non-overlapping) custom named ares (i.e. part of the WorkSheet.Names collection), is there any simple way I can detect the region that a selected cell might be part of? I could get the row and column index of the cell in question and then loop through all the Name objects in the collection, testing if it falls within the R and C range, but I was hoping there might be something simpler. Thanks in advance Craig |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect if a selected cell is within a named range
Note that this will work, but only if
a) all the Names in the workbook refer to ranges, and b) all the named ranges are on the active sheet. One workaround: Dim nmTest As Name Dim rTest As Range On Error Resume Next For Each nmTest In ActiveWorkbook.Names Set rTest = Intersect(ActiveCell, nmTest.RefersToRange) If Not rTest Is Nothing Then MsgBox nmTest.Name End If Next nmTest On Error GoTo 0 In article , "Bob Phillips" wrote: Dim nme As Name For Each nme In ActiveWorkbook.Names If Not Intersect(ActiveCell, Range(nme.Name)) Is Nothing Then MsgBox nme.Name End If On Error GoTo 0 Next nme |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
duplicate named ranges- how to detect, delete? | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
named cell range | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |