Create, modify and delete validation lists in Excel with VBA

Problem

We want to create, edit or delete validation lists in Excel using VBA.

Solución

• Creating a validation list

For the range $A$2:$A$5 we create a validation list based on $C$2:$C$5.

Sub Create_list()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$C$2:$C$5"
    End With
End Sub


• Modifying a validation list

For the range $A$2:$A$5 we modify the validation list source now based on $E$2:$E$5.

Sub Modify_list()
    With Range("$A$2:$A$5").Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$E$2:$E$5"
    End With
End Sub


• Deleting a validation list

We delete the validation lists for the specified range.

Sub Delete_list()
    With Range("$A$2:$A$5").Validation
        .Delete        
    End With
End Sub

If instead of a range, we'd like to apply the code to a selection:

'Replace Range("$A$2:$A$5").Validation for
Selection.Validation

References

• Validation.Modify method