Excel Ctrl Tilde To Show Named Range

Is there a way upon pressing ctrl+~ that instead of displaying the formula in each cell, display that cells respective name. So if there is no name for that cell, and name is defined as named range, the cell would just be blank.

thanks in advance!

thanks for the quick response!

anything thoughts on how to best "simulate" this event/action. basically, regardless of hotkey or button, for a given sheet - how would you be able to toggle between a state where named ranges where in cells and then return back...like answer mode.

thanks!

Given that named ranges can be more than one cell, here's a routine which toggles overlaying the named ranges on the active sheet with shapes.


Put code in standard module and assign shortcut.

Sub ShowNamedRange()
Dim nm As Name
Dim shp As Shape
Dim blnShowing As Boolean
Dim l&, t&, w&, h&

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, Len("Named Range")) = "Named Range" Then
        shp.Delete
        blnShowing = True
    End If
Next

If blnShowing = False Then
    For Each nm In ActiveWorkbook.Names
        If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
            With nm.RefersToRange
                l = .Left
                t = .Top
                w = .Width
                h = .Height
            End With
        
            With ActiveSheet.Shapes.AddShape(msoShapeRectangle, l, t, w, h)
                .Name = "Named Range " & nm.Name
                .Fill.ForeColor.RGB = RGB(80, 240, 180) 
               .TextFrame.Characters.Text = nm.Name
                .TextFrame.Characters.Font.ColorIndex = 1
            End With
        End If
    Next nm
End If
End Sub

thanks for the reply.

I receive Error 1004 "Application-defined or Object-defined error" on line "If nm.RefersToRange.Parent.name = ActiveSheet.name Then"

Do you have any names with invalid references?
I.E. if you go into Name Manager, anything with #REF or similar?

Modified to test the validity

Sub ShowNamedRange()
Dim nm As Name
Dim myName As String
Dim shp As Shape
Dim blnShowing As Boolean
Dim l&, t&, w&, h&

For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, Len("Named Range")) = "Named Range" Then
        shp.Delete
        blnShowing = True
    End If
Next

If blnShowing = False Then
    For Each nm In ActiveWorkbook.Names
        On Error Resume Next
        myName = ""
        myName = nm.RefersToRange.Parent.Name
        On Error GoTo 0
        If Len(myName) Then
            If nm.RefersToRange.Parent.Name = ActiveSheet.Name Then
                With nm.RefersToRange
                    l = .Left
                    t = .Top
                    w = .Width
                    h = .Height
                End With
            
                With ActiveSheet.Shapes.AddShape(msoShapeRectangle, l, t, w, h)
                    .Name = "Named Range " & nm.Name
                    .Fill.ForeColor.RGB = RGB(80, 240, 180)
                   .TextFrame.Characters.Text = nm.Name
                    .TextFrame.Characters.Font.ColorIndex = 1
                End With
            End If
        End If
    Next nm
End If
End Sub

Welcome

Thanks for the help!

worked perfectly.