If Range.Name question

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
Thanks Leo, Chip, J.E. and John. My forehead is getting flat from hitting
it with the palm of my hand. Otto
 
You're welcome, Otto.
I *told* the other guys, that we shouldn't
give you a hand. Now look :-))
 
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
 
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
 
Back
Top