Problems With Assigning A Named Range To The List Criteria of Conditional Formatting

[Ark68]

With VBA, I am failing at trying to assign a named range as the criteria for a list conditional formatting.

Here is my code where I prepare the cell's conditional formatting:

 With ws_staffrec
        .Unprotect
        With .Range("B6")
            .Value = ""
            .Interior.Color = RGB(218, 238, 243)
            Call build_emplnolist
            With .Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:=eno_list
                .IgnoreBlank = True
                .InCellDropdown = True
                .ShowInput = True
                .ShowError = True
            End With
        End With
        ...
End With

The line in red is giving me an "Application defined or object defined error". (I took this code from macro recorder)
eno_list is what I think is a named range. (It does appear valid in the names list)

 

[Fluff]

Try

Formula1:="=eno_list"

 

[Ark68]

Hi Fluff!!
Unfortunately, even with your improvement, I'm getting the same error:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=eno_list"

With my limited experience, I question why the operator would be xlBetween when the user selects only one value from the list?

 

[Ark68]

Maybe the problem is with the creation of the named range...

Sub build_emplnolist()
    
    Dim rng_enolist As Range
    Dim r As Long, d As Long
    
    On Error Resume Next
    ThisWorkbook.Names("eno_list").Delete
    With ws_vhs
        .Columns(1).ClearContents
        r = 1
        For d = 2 To 44
            If ws_roster.Cells(d, 3) <> "Vacancy" Then
                .Range("A" & r) = ws_roster.Cells(d, 1)
                r = r + 1
            End If
        Next d
        Set rng_enolist = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        ThisWorkbook.Names.Add Name:="eno_list", RefersTo:=rng_enolist
    End With

End Sub

 

[Fluff]

What is the actual range of eno_list?

 

[Ark68]

eno_list = rng_enolist = worksheets("VarHold") {ws_vhs}. range("A1:A last row of data in that column, in this case 30)

 

[Fluff]

Are you sure it's more than one cell?
Check in the name manager

 

[Ark68]

 

[Fluff]

In that case I'm not sure, as it works for me.
The only way I could get it to fail, was to have a range that was just A1

 

[Ark68]

I tracked down the cause of the error to be that the code was trying to apply data validation to a cell that already had it.
I did delete the name in advance to recreated it (its dynamic), but I did fail to clear the previous cell validation.

 With ws_staffrec
        .Unprotect
        Stop
        With .Range("B6")
            .Validation.Delete
            'If .MergeCells Then .MergeArea.UnMerge
            .Value = ""
            .Interior.Color = RGB(218, 238, 243)
            Call build_emplnolist
            With .Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=eno_list"

The addition of the line in purple appears to have eliminated the error.

Thanks so much for your willingness to help. Always appreciated!