If Range.Name question

O

Otto Moehrbach

Excel XP & Win XP
I want to check if the ActiveCell range name is "Hello". I thought this
little macro would work but it returns a "No". What is the correct way to
make this check? Thanks for your help. Otto
Sub TestName()
If ActiveCell.Name = "Hello" Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub
 
L

Leo Heuser

Hi Otto

You must have Name twice. The first
Name returns the address of the cell, and
the second Name returns the name of
that address (if any).

Sub TestName()
If ActiveCell.Name.Name = "Hello" Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub
 
C

Chip Pearson

Otto,

Try something like the following:

Dim S As String
On Error Resume Next
S = ActiveCell.Name.Name
On Error GoTo 0
If StrComp(S, "Hello", vbTextCompare) = 0 Then
MsgBox "Yes"
Else
MsgBox "No"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
J

J.E. McGimpsey

The Name object's default property is .Value, not .Name:

For instance, in the immediate window:

? ActiveCell.Name
=Sheet1!$A$1

? ActiveCell.Name.Value
=Sheet1!$A$1

?ActiveCell.Name.Name
Hello

So your code could be rewritten:

Sub TestName()
If ActiveCell.Name.Name = "Hello" Then
MsgBox "Yes"
Else
MsgBox "No"
End If
End Sub
 
J

John Wilson

Otto,

Remember Google ??? <vbg>

From a John Greene post.....

for a single cell range:
Activecell.Name.Name

for a multiple cell range:
Sub WhereAmI()
Dim n As Name
On Error Resume Next
For Each n In Names
If Intersect(ActiveCell, n.RefersToRange) Is Nothing Then
Else
MsgBox "Active cell is in " & n.Name
Exit Sub
End If
Next n
End Sub

John
 
O

Otto Moehrbach

Thanks Leo, Chip, J.E. and John. My forehead is getting flat from hitting
it with the palm of my hand. Otto
 
L

Leo Heuser

You're welcome, Otto.
I *told* the other guys, that we shouldn't
give you a hand. Now look :))
 
A

Andy Keen

This is an interesting one! The name property of a range
(or cell) is not the string it is named, but another name
object, so ActiveCell.Name.Name returns "Hello". However,
if the cell is not named at all, you will just get an
error. Not sure how you tell if a cell has a name attached
or not.

As long as you know the "Hello" cell exists the best way
is just to check if it is the ActiveCell - that is:

If ws.Range("Hello").Address = ActiveCell.Address Then
MsgBox ("YES")
......
.....

Regards, Andy
 
J

J.E. McGimpsey

Potential pitfall there, too: if ws isn't the activesheet, you may
get a false positive (i.e., cell named "Hello" is Sheet1!A1,
activecell is Sheet2!A1, then

Sheets("Sheet1").Range("Hello").Address = ActiveCell.Address

is True.

Another way:

Dim nmA As Name
Dim sMsg As String
sMsg = "No"
On Error Resume Next
Set nmA = ActiveCell.Name
On Error GoTo 0
If Not nmA Is Nothing Then _
If nmA.Name = "Hello" Then _
sMsg = "Yes"
MsgBox sMsg
 
Top