PDA

View Full Version : VBA Display Named Range



martynball
05-06-2010, 01:59 AM
Hey, I have got cells with different named ranges. Such as "J9" has the name of "u3p4".

I have then got this VBA code:

Sub displayTask()
Dim cell, selection As Variant
cell = Sheets("Marks Sheet").Range("task_display")
MsgBox (ActiveCell.Name)
End Sub

But for some reason instead of displaying "u3p4" it is displaying the actual location of the cell which is "='Marks Sheet'!$J$9"...

How cna I get it to get the actual name instead of the address....

omnibuster
05-06-2010, 02:50 AM
Try & modify
Sub NAMED()
Dim strName As String
Range("F1").Name = ("ARA")
strName = GetRangeName(ActiveCell)
If strName <> "" Then
MsgBox strName
End If
End Sub
Public Function GetRangeName(rngTest As Range) As String
Dim nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
If nm.RefersTo = rngTest.Name Then
GetRangeName = nm.Name
Exit Function
End If
Next nm
End Function

Bob Phillips
05-06-2010, 02:53 AM
You may not believe this :)



Activecell.Name.Name