CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21814

Run-time error 1004 when adding data validation. How do I avoid the error?

I am using the following procedure to add data validation to a cell:

Sub SetDataValidationList(r As Range, s As String)
    Dim dv As Validation
    Set dv = r.Validation

    With dv
        .Delete
        If s <> "" Then
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s
        Else
            'The backup string could be a parameter
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="None available"
        End If
    End With
End Sub

It worked while I was developing the Excel file for a few days, but now for some reason the row .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s throws run-time error 1004. What might be the reason? How can I avoid the error?

s is "1.1,PM,PA,OE,MOA1,MOA2,MOA3,MOA4"

Upvotes: 0

Views: 618

Answers (1)

user1283776
user1283776

Reputation: 21814

I just figured it out. The reason is that the sheet was protected.

Upvotes: 1

Related Questions