Display range name when a cell within the range is selected

K

Kiz

How to display the name of a range when a single cell within that named range
is selected.

Kathy
 
J

JE McGimpsey

one way:

This displays the named range(s) that the cell belongs to, if any, in
the Status bar.

Put this in the ThisWorkbook code module of your workbook.

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim sRanges As String
Dim nmName As Name
Dim bInRng As Boolean

On Error Resume Next
For Each nmName In ThisWorkbook.Names
Debug.Print nmName.Name, nmName.RefersToRange.Address
bInRng = Not Intersect(Target, nmName.RefersToRange) Is Nothing
If bInRng Then sRanges = sRanges & ", " & nmName.Name
Next nmName
On Error GoTo 0
If sRanges = "" Then
Application.StatusBar = False
Else
Application.StatusBar = Mid(sRanges, 3)
End If
End Sub

Private Sub Workbook_Deactivate()
Application.StatusBar = False
End Sub


If you're not familiar with macros, see David McRitchie's "Getting
Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top