Returning the range name in VBA
 

I have a macro that I would like to run based on whether the cursor i
on a particular cell, or more specifically, if it is on a particula
range name. I've tried something like:

If Activecell.Range.Name="Revenue" then DoSomething

and other variations, but no luck. I can't seem to find the way t
return the range name. BTW, Revenue is a one-cell range.

If Activecell.Address="$C$3"="Revenue" then DoSomethin


works, but I would like to be able to use the range name to facilitat
worksheet changes later.

Thanks for any help

--
John Tji
-----------------------------------------------------------------------
John Tjia's Profile: http://www.excelforum.com/member.php...nfo&userid=468
View this thread: http://www.excelforum.com/showthread.php?threadid=26848

Returning the range name in VBA
 

If ActiveCell.Address = Range("Revenue").Address Then

PS: the line below *should* work but it doesn't

If ActiveCell Is Range("Revenue")


On Tue, 12 Oct 2004 09:08:53 -0500, John Tjia
wrote:


I have a macro that I would like to run based on whether the cursor is
on a particular cell, or more specifically, if it is on a particular
range name. I've tried something like:

If Activecell.Range.Name="Revenue" then DoSomething

and other variations, but no luck. I can't seem to find the way to
return the range name. BTW, Revenue is a one-cell range.

If Activecell.Address="$C$3"="Revenue" then DoSomething


works, but I would like to be able to use the range name to facilitate
worksheet changes later.

Thanks for any help!

Returning the range name in VBA
 

I cobbled this together, but surely there is a more elegant way to d
it...

If ActiveWorkbook.Names("Revenue").RefersTo = "="
ActiveCell.Worksheet.Name & "!" & ActiveCell.Address Then
MsgBox "On Range"
End If

I don't use named ranges much, so take it for what it's worth.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26848

Returning the range name in VBA
 

In this situation I would use

If Activecell.Range.Name.Name="Revenue" then DoSomething

(note the name.name to return the name of the cell as you have defined it)

however, just today it started returning the name of the sheet and then the
cell and I can't work out why. I have a feeling it has something to do with
the exporting sheets and stuff like that.

Cheers!



"John Tjia" wrote:


I have a macro that I would like to run based on whether the cursor is
on a particular cell, or more specifically, if it is on a particular
range name. I've tried something like:

If Activecell.Range.Name="Revenue" then DoSomething

and other variations, but no luck. I can't seem to find the way to
return the range name. BTW, Revenue is a one-cell range.

If Activecell.Address="$C$3"="Revenue" then DoSomething


works, but I would like to be able to use the range name to facilitate
worksheet changes later.

Thanks for any help!


--
John Tjia
------------------------------------------------------------------------
John Tjia's Profile: http://www.excelforum.com/member.php...fo&userid=4681
View this thread: http://www.excelforum.com/showthread...hreadid=268487

Returning the range name in VBA
 

It is because the cell has a sheet level name rather than a workbook level
name.

Look at the name in Insert=Name=Define. It should have the sheet name on
the right hand side.

--
Regards,
Tom Ogilvy

"Rich" <rich@hotmail wrote in message
...
In this situation I would use

If Activecell.Range.Name.Name="Revenue" then DoSomething

(note the name.name to return the name of the cell as you have defined it)

however, just today it started returning the name of the sheet and then
the
cell and I can't work out why. I have a feeling it has something to do
with
the exporting sheets and stuff like that.

Cheers!



"John Tjia" wrote:


I have a macro that I would like to run based on whether the cursor is
on a particular cell, or more specifically, if it is on a particular
range name. I've tried something like:

If Activecell.Range.Name="Revenue" then DoSomething

and other variations, but no luck. I can't seem to find the way to
return the range name. BTW, Revenue is a one-cell range.

If Activecell.Address="$C$3"="Revenue" then DoSomething


works, but I would like to be able to use the range name to facilitate
worksheet changes later.

Thanks for any help!


--
John Tjia
------------------------------------------------------------------------
John Tjia's Profile:
http://www.excelforum.com/member.php...fo&userid=4681
View this thread:
http://www.excelforum.com/showthread...hreadid=268487