<< Click to Display Table of Contents >> Validation (object)

Access paths:

▪Application à Workbooks à Item à Sheets à Item à Range à Validation

▪Application à Workbooks à ActiveSheet à Range à Validation

▪Application à ActiveWorkbook à ActiveSheet à Range à Validation

▪Application à ActiveSheet à Range à Validation

Instead of "Range", you can also use other objects and properties that return a Range object: ActiveCell, Selection, Rows(n), Columns(n) and Cells(x, y). You can find examples of these access paths in the Range-Object.

 1  Description

The Validation object represents the validation check of a range (that is, a Range object). In PlanMaker, such validation checks can be set up with the ribbon command Review | Input validation.

 2  Access to the object

Each Range object has exactly one instance of the Validation object. It is accessed through Range.Validation:

' Display the input message for cell A1

MsgBox pm.ActiveSheet.Range("A1").Validation.InputMessage

 3  Properties, objects, collections and methods

Properties:

▪Type R/O

▪AlertStyle

▪Value R/O

▪ShowInput

▪InputTitle

▪InputMessage

▪ShowError

▪ErrorTitle

▪ErrorMessage

▪Operator R/O

▪Formula1 R/O

▪Formula2 R/O

▪InCellDropDown

▪IgnoreBlank

 

Objects:

▪Application → Application

▪Parent → Range

 

Methods:

▪Add

▪Modify

▪Delete

Type (property, R/O)

Data type: Long (PmDVType)

Gets or sets the setting which type of values to allow. The possible values are:

pmValidateInputOnly     = 0 ' Allow all types of values

pmValidateWholeNumber   = 1 ' Allow only integer numbers

pmValidateDecimal       = 2 ' Allow only decimal numbers

pmValidateList          = 3 ' Allow only values from a pre-defined list

pmValidateDate          = 4 ' Allow only date values

pmValidateTime          = 5 ' Allow only time values

pmValidateTextLength    = 6 ' Allow only values of a certain length

pmValidateCustom        = 7 ' User-defined check

AlertStyle (property)

Data type: Long (PmDVAlertStyle)

Gets or sets the style of the error message for invalid values.

pmValidAlertStop          = 0 ' Error message

pmValidAlertWarning       = 1 ' Warning message

pmValidAlertInformation   = 2 ' Information message

Value (property, R/O)

Data type: Boolean

Returns True, when the range contains valid values (i.e. values passing the input validation check), else False.

ShowInput (property)

Data type: Long

Gets or sets the setting if an input message should be displayed when the cell is activated. Corresponds to the setting "Show input message when cell is selected" on the Input message tab in the dialog box of the ribbon command Review | Input validation.

InputTitle (property)

Data type: String

Gets or sets the title of the input message that appears when the cell is activated. Corresponds to the entry field "Title" on the Input message tab in the dialog box of the ribbon command Review | Input validation.

InputMessage (property)

Data type: String

Gets or sets the text of the input message that appears when the cell is activated. Corresponds to the entry field "Message" on the Input message tab in the dialog box of the ribbon command Review | Input validation.

ShowError (property)

Data type: Long

Gets or sets the setting whether a message should be displayed when a value that do not pass the input validation check is entered into the cell. Corresponds to the setting "Show error message after invalid data is entered" on the Error message tab in the dialog box of the ribbon command Review | Input validation.

ErrorTitle (property)

Data type: String

Gets or sets the title of the message that is displayed when an invalid value is entered into the cell. Corresponds to the entry field "Title" on the Error message tab in the dialog box of the ribbon command Review | Input validation.

ErrorMessage (property)

Data type: String

Gets or sets the title of the message that is displayed when an invalid value is entered into the cell. Corresponds to the entry field "Message" on the Error message tab in the dialog box of the ribbon command Review | Input validation.

Operator (property, R/O)

Data type: Long (PmDVOperator)

Gets or sets the comparison operator used by the input validation check.

pmDVBetween        = 0 ' is between

pmDVNotBetween     = 1 ' is not between

pmDVEqual          = 2 ' is equal to

pmDVNotEqual       = 3 ' is not equal to

pmDVGreater        = 4 ' is greater than

pmDVLess           = 5 ' is less than

pmDVGreaterEqual   = 6 ' is greater than or equal to

pmDVLessEqual      = 7 ' is less than or equal to

Formula1 (property, R/O)

Data type: String

Returns the minimum of the validation check for the operators pmDVBetween and pmDVNotBetween. For all other operators, it returns the value.

Formula2 (property, R/O)

Data type: String

Returns the maximum of the validity check for the operators pmDVBetween and pmDVNotBetween, for all other operators the return value is empty.

InCellDropDown (property)

Data type: Long

Gets or sets the setting whether a list of the allowed values should be displayed in the cell. Applicable only when the type of validation check (see Type property above) is set to "List entries" (pmValidateList).

Corresponds to the option "Use dropdown" in the dialog box of the ribbon command Check | Validation.

IgnoreBlank (property)

Data type: Long

Gets or sets the setting whether empty cells should be ignored by the input validation check. Corresponds to the setting "Ignore empty cells" in the dialog box of the ribbon command Check | Validation.

Application (pointer to object)

Data type: Object

Returns the Application object.

Parent (pointer to object)

Data type: Object

Returns the parent object, i.e. an object of the type Range.

Add (method)

Sets up a new validity check in an area. Corresponds to the ribbon command Check | Validation.

Please note that each cell cannot have more than one input validation check.

Syntax:

 Add Type, [AlertStyle], [Operator], [Formula1], [Formula2]

Parameters:

 pmValidateInputOnly      = 0 ' Allow all types of values *

 pmValidateWholeNumber    = 1 ' Allow only integer numbers

 pmValidateDecimal        = 2 ' Allow only decimal numbers

 pmValidateList           = 3 ' Allow only values from a pre-defined list **

 pmValidateDate           = 4 ' Allow only date values

 pmValidateTime           = 5 ' Allow only times values

 pmValidateTextLength     = 6 ' Allow only values of a certain length

 pmValidateCustom         = 7 ' User-defined check ***

 pmValidAlertStop          = 0 ' Error message

 pmValidAlertWarning       = 1 ' Warning message

 pmValidAlertInformation   = 2 ' Information message

 pmDVBetween        = 0 ' is between

 pmDVNotBetween     = 1 ' is not between

 pmDVEqual          = 2 ' is equal to

 pmDVNotEqual       = 3 ' is not equal to

 pmDVGreater        = 4 ' is greater than

 pmDVLess           = 5 ' is less than

 pmDVGreaterEqual   = 6 ' is greater than or equal to

 pmDVLessEqual      = 7 ' is less than or equal to

Return type:

Summary of all parameter combinations possible:

Modify (method)

Modifies the input validation for a range.

Syntax:

 Modify [Type], [AlertStyle], [Operator], [Formula1], [Formula2]

Parameters:

 pmValidateInputOnly        = 0 ' Allow all types of values *

 pmValidateWholeNumber      = 1 ' Allow only integer numbers

 pmValidateDecimal          = 2 ' Allow only decimal numbers

 pmValidateList             = 3 ' Allow only values from a pre-defined list **

 pmValidateDate             = 4 ' Allow only date values

 pmValidateTime             = 5 ' Allow only times values

 pmValidateTextLength       = 6 ' Allow only values of a certain length

 pmValidateCustom           = 7 ' User-defined check ***

 pmValidAlertStop          = 0 ' Error message

 pmValidAlertWarning       = 1 ' Warning message

 pmValidAlertInformation   = 2 ' Information message

 pmDVBetween        = 0 ' is between

 pmDVNotBetween     = 1 ' is not between

 pmDVEqual          = 2 ' is equal to

 pmDVNotEqual       = 3 ' is not equal to

 pmDVGreater        = 4 ' is greater than

 pmDVLess           = 5 ' is less than

 pmDVGreaterEqual   = 6 ' is greater than or equal to

 pmDVLessEqual      = 7 ' is less than or equal to

Return type:

Delete (method)

Removes the input validation check from a range.

Syntax:

 Delete

Parameters:

Return type:

Example:

' Remove the input validation check from cells A1 and A2

pm.Application.ActiveSheet.Range("A1:A2").Validation.Delete