Reports

Blacklisted phrase (1): stackoverflow
Long answer (-0.5):
Has code block (-0.5):
User mentioned (1): user6432984
Low reputation (1):

Don't want to necropost but the original (marked) answer from user6432984 generates errors when there are named ranges in other sheets (On Error Resume Next simply moves to the statement after "Then", as per https://stackoverflow.com/a/40552264, which results the said ranges to be returned as part of the intersection, which is obviously wrong). In addition, VBA returns a bunch of internal names (beginning with "_xl") in certain scenarios which are a distraction. I have modified the original answer to this:

Function getRangeNames(Target As Range)
    Dim n As Name
    Dim s As String
    Dim intersection As Range
    For Each n In ThisWorkbook.Names
        If Left(n.Name, 3) <> "_xl" Then
            On Error Resume Next
            Set intersection = Intersect(Target, n.RefersToRange)
            On Error GoTo 0
            If Not intersection Is Nothing Then
                s = s & n.Name & ", "
            End If
        End If
    Next n
    
    getRangeNames = Left(s, Len(s) - 2)
End Function

79578901