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

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

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

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

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

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