Unable to use Validation

[Mister_joe]

Hi all,
I am trying to apply validation to a range of cells as follows:

Private Sub Worksheet_Activate()   
    With Range("A1:D4").Validation
           .IgnoreBlank = True
           .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:="0", Formula2:="50000"
           .InputTitle = "Numeric"
           .ErrorTitle = "Numeric"
           .InputMessage = "Enter a number between 0 and 50,000"
           .ErrorMessage = "You must enter a number between 0 and 50,000"
   End With
    
End Sub

When I executed the code, I got the following error message:


Run-Time error '1004'
Application-defined or object-defined error




Thereafter, I move the code the Open event of the Workbook as follows:

Private Sub Workbook_Open()   
    With Range("A1:D4").Validation
           .IgnoreBlank = True
           .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:="0", Formula2:="50000"
           .InputTitle = "Numeric"
           .ErrorTitle = "Numeric"
           .InputMessage = "Enter a number between 0 and 50,000"
           .ErrorMessage = "You must enter a number between 0 and 50,000"
   End With
    
End Sub

I got the same error message. So, I wish to know in what context I am supposed to use validation.


Thanks.

[snb]

With Range("A1:D4").Validation.add

or if the validation exists:

With Range("A1:D4").Validation.Modify

[Mister_joe]

what do you mean by "if Validation exists"?

[snb]

Alt-F11 / F1 / validation.modify / Enter

[Mister_joe]

Apology for coming back late. Here's where the help and recording the validation process lead me:

Private Workbook_Open() 
Range("A1:D4").Select
With Selection.Validation 
        .IgnoreBlank = True 
        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:="0", Formula2:="50000" 
        .InputTitle = "Numeric" 
        .ErrorTitle = "Numeric" 
        .InputMessage = "Enter a number between 0 and 50,000" 
        .ErrorMessage = "You must enter a number between 0 and 50,000" 
End Sub

[Aussiebear]

How is that different from that which you initially offered? Did you try the concept offered by snb?

[Mister_joe]

I followed the Alt-F11 / F1 / validation.modify / Enter and found helpful remarks on Validation object. As far as I am concerned, the invocation of the Select method made the difference. I may be wrong, but my challenge with applying validation on a range has been resolved just by invoking the Select method.

[Aussiebear]

invoking the select method simply slows the process down. Ask any experienced coder about using select, and they will tell you to ditch it. Anywhere you see .select With selection smacks of macro recorder code and that's a no no.

 With Range("A1:D4").validation

Now, earlier snb offered you two options, one was .add (if validation did not already exist) and two, .modify ( if it already existed)mMy question you is, is the validation new or pre- existing? If its new then use validation.add, if not then use validation.modify

[Mister_joe]

Once more, apology for responding late. Could you give me an example code, please. The following came up with error message: "Compiler error, argument not optional"

With Range("A1:D4").Validation.Add         
        .IgnoreBlank = True 
        .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertWarning, Operator:=xlBetween, Formula1:="0", Formula2:="50000" 
        .InputTitle = "Numeric" 
        .ErrorTitle = "Numeric" 
        .InputMessage = "Enter a number between 0 and 50,000" 
        .ErrorMessage = "You must enter a number between 0 and 50,000" 
End With

[snb]

This might be of interest before continuing:

http://it-ebooks.info/book/1903/

[Mister_joe]

Thanks for the reference.