Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a range of cells from a value | Excel Worksheet Functions | |||
Returning 2nd smallest value in a range | Excel Worksheet Functions | |||
IF Function Returning Range | Excel Discussion (Misc queries) | |||
Returning a range using IF | Excel Worksheet Functions | |||
Returning a range | Excel Discussion (Misc queries) |