Data Validation List Error

Question

I'm trying to add a data validation pulldown list, and I've been debugging it for a while to no avail. I'm getting a Run-time error 1004, Application-defined or object defined error. The error occurs in Formula1 setting part of the Validation.Add statement.

I've tried using a string reference to a named range, a string reference to a standard range, and, as shown below, a comma delimited list string generated from the list on the worksheet as shown in the code below. I have checked the list string with Debug.Print and got the expected result.

Sub addPT_Validation()
Dim sValidationList As String
Dim cell As Range

For Each cell In ThisWorkbook.Names("PT_Puldown").RefersToRange
    sValidationList = sValidationList & cell.Value & ","
Next cell

sValidationList = Left(sValidationList, Len(sValidationList) - 1)

With ActiveSheet.Range("D14").Validation
    .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlEqual, _
            Formula1:=sValidationList
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowError = True
End With
End Sub

Thanks in advance for any guidance.

Answer 1

All your code is working (certainly could be optimized), all you need to do is delete the validation if it already exists.

If you try to add validation to a cell which already contains one, you will get error 1004.

Sub addPT_Validation()
    Dim sValidationList As String
    Dim cell As Range

    For Each cell In ThisWorkbook.Names("PT_Puldown").RefersToRange
        sValidationList = sValidationList & cell.Value & ","
    Next cell

    sValidationList = Left(sValidationList, Len(sValidationList) - 1)

    With ActiveSheet.Range("D1").Validation
        '/Delete first., in case of any any existing validation
        .Delete

        .Add Type:=xlValidateList, _
                AlertStyle:=xlValidAlertStop, _
                Operator:=xlEqual, _
                Formula1:=sValidationList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowError = True
    End With
End Sub

Answer 2

I'd use the named range reference to build up the formula to put in Formula1 parameter of Validation object Add() method, like follows

Sub addPT_Validation()
Dim formula As String

With ThisWorkbook.Names("PT_Puldown").RefersToRange
    formula = "'" & .Parent.Name & "'!" & .Address(External:=False) 
End With

With ActiveSheet.Range("D14").Validation
    .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlEqual, _
            Formula1:=formula
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowError = True
End With
End Sub

Answer 3

As pointed out by @cyboashu, your error is coming from the fact that you did not delete the validation before adding a new one.

With regard to your other issue of trying to pass the range to Formula1 directly:

Formula1 should refer to the range as a string. eg:

Formula1:="=$G$2:$G$7"

or, using your variables:

Formula1:="=" & ThisWorkbook.Names("PT_Puldown").RefersToRange.Address

or, simpler:

Formula1:="=" & Range("PT_Puldown").Address