VBA for data validation --> Method 'Add' of object 'Validation' failed

**bagullo** · 09-10-2010, 06:02 AM

for some reason I can't get it to work it wright
even copiying the code from the help file will not work

any clue what could be going on?

With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertInformation, _
Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With

error number -2147417848
error helpcontext 1000440

error description : Method 'Add' of object 'Validation' failed

**Marcol** · 09-10-2010, 06:18 AM

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

Then try this

      With Range("E5").Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="5", Formula2:="10"
        .InputTitle = "Integers"
        .ErrorTitle = "Integers"
        .InputMessage = "Enter an integer from five to ten"
        .ErrorMessage = "You must enter a number from five to ten"
    End With

Hope this helps