CODEWITHSUNDEEP
vbaexcel
Tanmay
Tanmay

Reputation: 31

How to check in the SheetChange event whether the change made was in a named Range

So I am coding in the SheetChange event in Excel. I want to know if the change was made in a named Range. So essentially: 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Some code
If ('Target address is a part of one of the named ranges')
    'Some code here

Note that I have multiple Named Ranges in different Sheets which I would like to check. One way can be to loop through every named range whenever a change is made somewhere. But I don't think that's a good way to code. Any help would be appreciated.

Upvotes: 1

Views: 211

Answers (1)

Olly
Olly

Reputation: 7891

Try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Some code
    If Not Intersect(Target, Range("MyNamedRange")) Is Nothing Then
        'Some code here
    End If

EDIT:

The above works to test if the target is part of one specific named range. If your requirement is to test whether the target is part of ANY named range, then you will indeed have to loop through the existing names - something like this would work:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Some code
    Dim sName As String
    sName = InAnyNamedRange(Target)
    If Not sName = "" Then
        'Some code here
        Debug.Print Target.Address & " changed, and is in named range " & sName
    End If
End Sub

Function InAnyNamedRange(ByRef rng As Range)
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        If Not Intersect(rng, Range(nm)) Is Nothing Then
            InAnyNamedRange = nm.Name
            Exit Function
        End If
    Next nm
End Function

Upvotes: 4

Related Questions