Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
I am trying to determine at runtime whether the selected cell has a range
name, and what that name is. This is to error trap the user accidentally selecting a row that is a category header (column A of these rows are named) as opposed to a row containing content (column A not named). If I use Range("A" & intRow).Name it shows "=Sheet!$A$13" instead of the name I'm looking for Any help appreciated. Bill Le May |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
In your Worksheet.selectionchange event, you could put something like this
If not intersect(target,range("RangeName1")) is nothing then Msgbox("You've selected a cell within RangeName1") end if "Bill" wrote: I am trying to determine at runtime whether the selected cell has a range name, and what that name is. This is to error trap the user accidentally selecting a row that is a category header (column A of these rows are named) as opposed to a row containing content (column A not named). If I use Range("A" & intRow).Name it shows "=Sheet!$A$13" instead of the name I'm looking for Any help appreciated. Bill Le May |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
You need to look for the Name property of the name object (yes its
confusing!) Range("A" & intRow).Name.Name Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Bill" wrote in message ... I am trying to determine at runtime whether the selected cell has a range name, and what that name is. This is to error trap the user accidentally selecting a row that is a category header (column A of these rows are named) as opposed to a row containing content (column A not named). If I use Range("A" & intRow).Name it shows "=Sheet!$A$13" instead of the name I'm looking for Any help appreciated. Bill Le May |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
Range("A" & intRow).Name
refers to the Name object associated with the cell. The default property for the .Name object is the .RefersTo property, so that's what you're getting. The Name's Name property is returned by Range("A" & intRow).Name.Name If you want to test whether the cell has a name, and return the name if it does, here's one way: Public Function CellName(ByRef rCell As Range) As String Dim nm As Name Dim rTest As Range Dim sResult As String On Error GoTo ErrorHandler For Each nm In ActiveWorkbook.Names Set rTest = nm.RefersToRange If Not rTest Is Nothing Then With rTest If .Parent.Name = rCell.Parent.Name Then If Not Intersect(.Cells, rCell) Is Nothing Then sResult = nm.Name Exit For End If End If End With End If Next nm If sResult = vbNullString Then For Each nm In rCell.Parent.Names Set rTest = nm.RefersToRange If Not rTest Is Nothing Then If Not Intersect(rTest.Cells, rCell) Is Nothing Then sResult = nm.Name Exit For End If End If Next nm End If If sResult < vbNullString Then CellName = sResult ResumeHe Exit Function ErrorHandler: If Err.Number = 1004 Then 'name doesn't refer to a range Set rTest = Nothing Resume Next Else CellName = CVErr(xlErrValue) Resume ResumeHere End If End Function The function returns a null string if the cell is not part of a named range. In article , "Bill" wrote: I am trying to determine at runtime whether the selected cell has a range name, and what that name is. This is to error trap the user accidentally selecting a row that is a category header (column A of these rows are named) as opposed to a row containing content (column A not named). If I use Range("A" & intRow).Name it shows "=Sheet!$A$13" instead of the name I'm looking for Any help appreciated. Bill Le May |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
Oops, took this from one of my previous applications and forgot to make
one change. Change For Each nm In ActiveWorkbook.Names to For Each nm In rCell.Parent.Parent.Names In article , JE McGimpsey wrote: Range("A" & intRow).Name refers to the Name object associated with the cell. The default property for the .Name object is the .RefersTo property, so that's what you're getting. The Name's Name property is returned by Range("A" & intRow).Name.Name If you want to test whether the cell has a name, and return the name if it does, here's one way: Public Function CellName(ByRef rCell As Range) As String Dim nm As Name Dim rTest As Range Dim sResult As String On Error GoTo ErrorHandler For Each nm In ActiveWorkbook.Names Set rTest = nm.RefersToRange If Not rTest Is Nothing Then With rTest If .Parent.Name = rCell.Parent.Name Then If Not Intersect(.Cells, rCell) Is Nothing Then sResult = nm.Name Exit For End If End If End With End If Next nm If sResult = vbNullString Then For Each nm In rCell.Parent.Names Set rTest = nm.RefersToRange If Not rTest Is Nothing Then If Not Intersect(rTest.Cells, rCell) Is Nothing Then sResult = nm.Name Exit For End If End If Next nm End If If sResult < vbNullString Then CellName = sResult ResumeHe Exit Function ErrorHandler: If Err.Number = 1004 Then 'name doesn't refer to a range Set rTest = Nothing Resume Next Else CellName = CVErr(xlErrValue) Resume ResumeHere End If End Function The function returns a null string if the cell is not part of a named range. In article , "Bill" wrote: I am trying to determine at runtime whether the selected cell has a range name, and what that name is. This is to error trap the user accidentally selecting a row that is a category header (column A of these rows are named) as opposed to a row containing content (column A not named). If I use Range("A" & intRow).Name it shows "=Sheet!$A$13" instead of the name I'm looking for Any help appreciated. Bill Le May |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does the selected cell have a range name?
Thanks to all for the help!
Bill Le May "Charles Williams" wrote in message ... You need to look for the Name property of the name object (yes its confusing!) Range("A" & intRow).Name.Name Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining Range of selected cell | Excel Programming | |||
Referencing the First Cell in Any Selected Range | Excel Programming | |||
Macro run if and cell in range is selected in VBA | Excel Programming | |||
Macro run if and cell in range is selected in VBA | Excel Programming | |||
Activate last cell in selected range - an example | Excel Programming |