Add data validation to table column via VBA

[juanbolas]

Hello,

I'm stumped. I'm trying to add validation to a particular column in a table and am getting a runtime error 1004 Application-defined or object-defined error in the '.ADD Type:= ...' line.

Any help is greatly appreciated. Code follows.

Thanks in advance

Sub ABS_Validation()

    Dim LR As Long
    TargetWS = "DB_ABS"
    TargetColumn = "ABS_Data[[#Headers],[N° de Orden]]"
    FR = Sheets(TargetWS).Range(TargetColumn).Row + 1
    LR = Sheets(TargetWS).Range(TargetColumn).End(xlDown).Row
    ValidationRange = "U" & FR & ":U" & LR
    Debug.Print ValidationRange

    Set ws = ActiveWorkbook.Sheets(TargetWS)
    Set ApprovalsValidation = ws.Range(ValidationRange)
    
    ValidationList = Chr(160) & ",Aprobado,Rechazado"
    
    With ApprovalsValidation.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:=ValidationList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = False
        .ShowError = True
    End With

End Sub

 

[davesexcel]

Your named range should be in quotes.

 Set ApprovalsValidation = ws.Range("ValidationRange")

 

[juanbolas]

Hello @davesexcel. I tried it and it gives me an error on the line where I add quotation marks. ValidationRange is defined near the beginning of the code

 

[davesexcel]

I can reproduce the problem, you may have to link a sample workbook

 

[Fluff]

As ValidationRange is a variable it should not be in quotes.

Is the "DB_ABS" sheet protected?

 

[juanbolas]

Hi @Fluff. I removed the quotes and it worked fine. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.