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.

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



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

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

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

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

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