Microsoft Office Tutorials and References
In Depth Information
Table 8-4. The Properties and Methods of the Validation Object
Attribute
Description
Method
Add
A method to create a validation rule for a range of cells. Table 8-5
gives you more information on which parameters you need to set.
Delete
A method that deletes a Validation object.
Modify
A method that changes the validation rules for a range of cells.
The Modify method uses the same parameter rules listed in
Table 8-5.
Which parameters of the Add and Modify methods you use depends on the type of value you
want to validate. The corresponding values for each validation type are shown in Table 8-5.
Table 8-5. The List of Parameters Used in an Add or Modify Statement
Validation Type
Parameters
xlValidateCustom
Formula1 is required, whereas Formula2 is never
used. (Any value assigned to Formula2 is
ignored.) Formula1 must contain an expression
that evaluates to True when data entry is valid
and False when data entry is invalid.
xlInputOnly
AlertStyle , Formula1 , or Formula2 are used.
xlValidateList
Formula1 is required, but Formula2 is never used.
(Any value assigned to Formula2 is ignored.)
Formula1 must contain either a comma-delimited
list of values or a worksheet reference to this list.
xlValidateWholeNumber, xlValidateDate,
One of either Formula1 or Formula2 must be
xlValidateDecimal, xlValidateTextLength,
specified, or both may be specified.
or xlValidateTime
If you wanted to set validation criteria for 600 cells in the H column of a worksheet, you
could use the following code to do so.
With Range("H6, H606").Validation
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="5000"
.InputTitle = "Credit Limit"
.ErrorTitle = "Credit Limit Too High"
.InputMessage = "Enter the customer's credit limit."
.ErrorMessage = "The credit limit must be less than $5,000."
End With
Search JabSto ::




Custom Search