Returning the range name in VBA

[John Tjia]

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

 

[Myrna Larson]

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

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

If ActiveCell Is Range("Revenue")

 

[kkknie]

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.

 

[Guest]

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!

 

[Tom Ogilvy]

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.