(Solved) – Excel VBA forces me to use Select when adding data validation in cells

(solved)-–-excel-vba-forces-me-to-use-select-when-adding-data-validation-in-cells

VBA is forcing me to use Select in order to be able to add data validation to a cell.

The way I wanted to do it is:

R is a Range received as a Parameter in the Sub since it is dynamically taken.

  With Cells(R.Row, 5).Validation
    .Delete
    .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
    Operator:=xlLessEqual, Formula1:="=TODAY()"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = "Error"
    .InputMessage = ""
    .ErrorMessage = "Fecha incorrecta"
    .ShowInput = False
    .ShowError = True
 End With

It stops in the .Add function giving a 1004 error.

The way it forces me to do it is:

Cells(R.row, 5).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, _
    Operator:=xlLessEqual, Formula1:="=TODAY()"
    .IgnoreBlank = True
    .InCellDropdown = False
    .InputTitle = ""
    .ErrorTitle = "Error"
    .InputMessage = ""
    .ErrorMessage = "Fecha incorrecta"
    .ShowInput = False
    .ShowError = True
End With

Leave a Reply

Your email address will not be published. Required fields are marked *