CODEWITHSUNDEEP
validationexcelvba
Angelo Lan
Angelo Lan

Reputation: 1

Run-time Error 1004 on data validation

I have the following code to validate the input of a cell to be a whole number between 0 and 10:

Range("c4").Activate
With ActiveCell.Validation
    .Add Type:=xlValidateWholeNumber, _
         AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, _
         Formula1:="0", _
         Formula2:="10"
    .InputTitle = "integers"
    .ErrorTitle = "Invalid Rating"
    .InputMessage = "Enter a whole number between 0 and 10"
    .ErrorMessage = "You must enter a whole number between 0 and 10"
End With

However, every time I run it the Run-time error 1004 shows up and it says application-defined or object-defined error. I have checked other posts but couldn't figure out exactly what is the problem since this should be a very straightforward code.

Upvotes: 0

Views: 1818

Answers (1)

djikay
djikay

Reputation: 10648

I think you slightly misunderstood the operation.

Your code will work properly the first time it is run and will set the validation rules as expected. Subsequent times it won't work, giving you the error you're seeing, because validation for cell C4 is already set up.

If you check cell C4 in your sheet, you'll see that the validation you entered actually works.

To remove the validation you added, you do: Range("c4").Validation.Delete. Then if you run your original code again, it will work and you'll be able to see the result in cell C4 as before.

Upvotes: 2

Related Questions