Error In Assigning a Name To A Set Range

[Ark68]

In my condensed code below a worksheet change event of cell K6 triggers code which defines a particular range basedon the value of K6. That newly defined range is named, and that named range is used in a data validation list for another cell (R6)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nr_div As Range

    If Target.Address = "$K$6" Then
        mbevents = False
        With ws_form
            .Unprotect
            With .Range("R6, V6")
                .Value = ""
                .Interior.Color = RGB(189, 215, 238)
                .Borders.Color = RGB(48, 84, 150)
                If .MergeCells = True Then
                    Set mergeRange = .MergeArea
                    mergeRange.Locked = True
                End If
                .Validation.Delete
            End With
        End With
        leag = ws_form.Range("K6").Value
       
         if leag = "WLU" Or leag = "UW" Then
            Set nr_calibre = ws_lists.Range("G5:G9")
            Set nr_div = ws_lists.Range("H22:H24")
            t = 1
        Elseif ...
            ....
        End if
        
        ActiveWorkbook.Names.Add Name:="nr_calibre2", RefersTo:=nr_calibre
       
         With ws_form.Range("R6")
            .Validation.Delete
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = False
            End If
            .Value = ""
            .Interior.Color = RGB(221, 235, 247)
            .Borders.Color = RGB(48, 84, 150)
            .Validation.Add Type:=xlValidateList, Formula1:="=nr_calibre2"
            .Select
        End With

        ws_form.Protect
        mbevents = True
    End If

    If Target.Address = "$R$6" Then
        mbevents = False

        With ws_form  'reset v6
            .Unprotect
            With .Range("V6")
                .Value = ""
                .Interior.Color = RGB(189, 215, 238)
                .Borders.Color = RGB(48, 84, 150)
                If .MergeCells = True Then
                    Set mergeRange = .MergeArea
                    mergeRange.Locked = True
                End If
                .Validation.Delete
            End With
        End With
        calibre = ws_form.Range("R6").Value

        With ws_form.Range("R6") 'prepare for user entry, data validation list
            .Interior.Color = RGB(198, 244, 180)
            .Borders.Color = RGB(55, 86, 35)
        End With
        ActiveWorkbook.Names.Add Name:="nr_division", RefersTo:=nr_div   'define name using set range assigned with K6 change. ERROR: "Application-defined or object-defined error"
        With ws_form.Range("V6")
            .Validation.Delete 'delete any validations assigned to cell V6 before applying a new one
            If .MergeCells = True Then
                Set mergeRange = .MergeArea
                mergeRange.Locked = False
            End If
            .Value = ""
            .Interior.Color = RGB(221, 235, 247)
            .Borders.Color = RGB(48, 84, 150)
            .Validation.Add Type:=xlValidateList, Formula1:="=nr_division"
            .Select
        End With
        ws_form.Protect
        mbevents = True
    End If
End Sub

I am receiving an "Application-defined or object-defined error" with the line highlighted red in the code above when trying to name the range set previously to nr_div.

Not understanding where I have erred.

 

[Fluff]

You've commented out this line

 .Validation.Delete

Put it back in

 

[Ark68]

Fluff, you're my hero. Thanks you! After so many days I'm finally able to spend my time advancing instead of trial and error to resolve a barrier.
Your help of course is appreciated!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Ark68]

But Fluff, I have came across one issue. Similar, but not the same.

Case Is = "WMBA", "KMBA"
            Set a1 = ws_lists.Range("G2:G3")
            Set a2 = ws_lists.Range("G7:G9")
            Set nr_calibre = Union(a1, a2)

Else 't=4
            ActiveWorkbook.Names.Add Name:="nr_calibre2", RefersTo:=nr_calibre
            With ws_form.Range("R6") 'division unlocked
                .Validation.Delete
                .Interior.Color = RGB(221, 235, 247)
                .Borders.Color = RGB(48, 84, 150)
                If .MergeCells = True Then
                    Set mergeRange = .MergeArea
                    mergeRange.Locked = False
                End If
                .Value = ""
                .Validation.Add Type:=xlValidateList, Formula1:="=nr_calibre2"
            End With
        End If

"Application defined or object defined error" with the line in red. I think it has something to do with the use of UNION in the setting of variable nr_calibre.

If I simply use:

Case Is = "WMBA", "KMBA"
            Set nr_calibre = ws_lists.Range("G2:G3")

I don't get the error, although the nr_calibre range isn't accurate for what I need in the data validation list based on nr_calibre.

 

[Fluff]

I don't think you can use non-contiguous ranges with data validation.

 

[Ark68]

OK Fluff, I will create a new range.