PDA

View Full Version : [SOLVED] Unable to use Validation



Mister_joe
02-14-2014, 02:29 AM
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
02-14-2014, 03:23 AM
With Range("A1:D4").Validation.add

or if the validation exists:


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

Mister_joe
02-14-2014, 03:32 AM
what do you mean by "if Validation exists"?

snb
02-14-2014, 03:42 AM
Alt-F11 / F1 / validation.modify / Enter

Mister_joe
02-16-2014, 12:05 PM
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
02-16-2014, 03:07 PM
How is that different from that which you initially offered? Did you try the concept offered by snb?

Mister_joe
02-17-2014, 03:02 AM
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
02-17-2014, 03:38 AM
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
02-18-2014, 08:59 AM
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
02-18-2014, 09:24 AM
This might be of interest before continuing:

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

Mister_joe
02-18-2014, 04:59 PM
Thanks for the reference.