Office Object Model: Excel

Office Object Model: Excel - Validation

The Validation object allows to constrain the possible values that can be entered in a range.

Although the method add implies that there can be added mulitpe validation rules for a range or cell, this is not the case. A cell can have at most one validation rule.

Properties and methods

`add()`
`alertStyle`	read-only, returns a value of the `xlDVAlertStyle` enumeration.
`application`
`creator`
`delete()`
`errorTitle` and `errorMessage`	The title and text of the message box that is displayed when the data validation rule is violated and `showError` is `true`
`formula1`	read-only.
`formula2`	read-only, only used if `operator` is `xlBetween` or `xlNotBetween`
`ignoreBlank`	A boolean: if `true`, blank values or not validated.
`IMEMode`
`inCellDropdown`	A boolean: if `true` and `type` is `xlValidateList`, the valid values can be chosen from a drop-down control
`inputTitle`, `inputMessage`	The title and text of the tool tip that is associated with the cell. The tool tip is only shown if `showInput` is set to `true`.
`modify()`
`operator`	`xlBetween`, `xlEqual`, `xlGreater`, `xlGreaterEqual`, `xlLess`, `xlLessEqual`, `xlNotBetween` or `xlNotEqual`
`parent`
`showError`	A boolean: if `true`, a message box is shown if an entered value violates the validation rule.
`showInput`	A boolean that specifies if the tool-tip that is associated with a validation rule is displayed.
`type`	`xlValidateCustom`, `xlValidateDate`, `xlValidateDecimal`, `xlValidateInputOnly`, `xlValidateList`, `xlValidateTextLength`, `xlValidateTime` or `xlValidateWholeNumber`. If `type` is `xlValidateCustom`, the value of `formula1` is a formula that evaluates `true` or `false`.
`value`

Example: Validation rule that checks for numbers

The following VBA example tries to demonstrate how to assign a data validation rule to a range so that the user cannot enter text.

Note: a user still can copy/paste text from another cell into the range (which simply overwrites the rule).

option explicit

sub main() ' {

   dim rng as range
   set rng = range(cells(4,3), cells(7, 7))
   rng.borderAround xlContinuous, xlMedium, color := rgb(140, 90, 180)

   setValidation rng

end sub ' }


sub setValidation(rng as range) ' {

    dim firstCellRelativeAddress as string
    dim formula                  as string

    firstCellRelativeAddress =  rng.address(rowAbsolute := false, columnAbsolute := false)
    formula                  = "=isNumber(" & firstCellRelativeAddress & ")"

    with rng.validation ' {

        .add type := xlValidateCustom, formula1 := formula

        .ignoreBlank  =  true

        .showInput    =  true
        .inputTitle   = "Validation rule"
        .inputMessage = "Enter a numerical value"

        .showError    =  true
        .errorTitle   = "Validation rule failed"
        .errorMessage = "Please enter a number""

    end with ' }

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Validation/only-numbers.vb

Office Object Model: Excel - Validation

Properties and methods

Example: Validation rule that checks for numbers

See also