How to get a cells' range name

[gimme_this_gimme_that@yahoo.com]

Suppose I make a range consisting of a single cell ...

Is there a way to get the name of the range of that cell from an
ActiveSheet in VBA ?

Thanks.

[Dave Peterson]

Maybe...

Option Explicit
Sub testme()

Dim myName As String
myName = ""
On Error Resume Next
myName = ActiveCell.Name.Name
On Error GoTo 0

If myName = "" Then
MsgBox "No name"
Else
MsgBox myName
End If
End Sub



gimme_this_gimme_that@yahoo.com wrote:
>
> Suppose I make a range consisting of a single cell ...
>
> Is there a way to get the name of the range of that cell from an
> ActiveSheet in VBA ?
>
> Thanks.

--

Dave Peterson

[Leith Ross]

Hello gimme_this_gimme_that,

The ActiveCell.Name.Name method doesn't work on all versions of Windows. This method isn't as short but should work with any Windows version.

Sub GetCellRange()

Dim Rng As Range
For N = 1 To ActiveWorkbook.Names.Count
Set Rng = ActiveWorkbook.Names(N).RefersToRange
If Intersect(ActiveCell, Rng) Is Nothing = False Then
MsgBox "Cell belongs to the Named Range " Rng.Name.Name
Else
MsgBox "Cell doesn't belong to a Named Range"
End If
Next N

End Sub

Sincerely,
Leith Ross

[Dave Peterson]

What version of windows (and excel) has it failed?

Leith Ross wrote:
>
> Hello gimme_this_gimme_that,
>
> The ActiveCell.Name.Name method doesn't work on all versions of
> Windows. This method isn't as short but should work with any Windows
> version.
>
> Sub GetCellRange()
>
> Dim Rng As Range
> For N = 1 To ActiveWorkbook.Names.Count
> Set Rng = ActiveWorkbook.Names(N).RefersToRange
> If Intersect(ActiveCell, Rng) Is Nothing = False Then
> MsgBox "Cell belongs to the Named Range " Rng.Name.Name
> Else
> MsgBox "Cell doesn't belong to a Named Range"
> End If
> Next N
>
> End Sub
>
> Sincerely,
> Leith Ross
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=512028

--

Dave Peterson

[Leith Ross]

Hello Dave,

I'm running Excel 2000 on Windows XP.

Sincerely,
Leith Ross

[Dave Peterson]

I'm surprised that you've had trouble with this. To a casual observer, I
wouldn't guess that the OS would have anything to do with this.

I never had any trouble with xl2k with WinNT.

Leith Ross wrote:
>
> Hello Dave,
>
> I'm running Excel 2000 on Windows XP.
>
> Sincerely,
> Leith Ross
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=512028

--

Dave Peterson