Page 215 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

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
Chapter 8
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

189