2.4.95 Dv

  • Article

The Dv record specifies a single set of data validation criteria defined for a range on this sheet.


0


1


2


3


4


5


6


7


8


9

1
0


1


2


3


4


5


6


7


8


9

2
0


1


2


3


4


5


6


7


8


9

3
0


1

valType

A

B

C

D

mdImeMode

E

F

G

reserved

PromptTitle (variable)

...

ErrorTitle (variable)

...

Prompt (variable)

...

Error (variable)

...

formula1 (variable)

...

formula2 (variable)

...

sqref (variable)

...

valType (4 bits): An unsigned integer that specifies the type of data validation. MUST be a value from the following table:

Value

Meaning

0x0

Specifies that the data validation allows any type of value and does not check for a type or range of values.

0x1

Specifies that the data validation checks for and allows whole number values satisfying the given condition.

0x2

Specifies that the data validation checks for and allows decimal values satisfying the given condition.

0x3

Specifies that the data validation checks for and allows a value that matches one in a list of values.

0x4

Specifies that the data validation checks for and allows date values satisfying the given condition.

0x5

Specifies that the data validation checks for and allows time values satisfying the given condition.

0x6

Specifies that the data validation checks for and allows text values whose length satisfies the given condition.

0x7

Specifies that the data validation uses a custom formula (section 2.2.2) to check the cell value.

A - errStyle (3 bits): An unsigned integer that specifies the style of error alert to be used in the error message which is shown if the fShowErrorMsg bit is set to 1. MUST be a value from the following table:

Value

Meaning

0x0

Specifies that the data validation error style uses a stop icon in the error alert.

0x1

Specifies that the data validation error style uses a warning icon in the error alert.

0x2

Specifies that the data validation error style uses an information icon in the error alert

B - fStrLookup (1 bit): A bit that specifies whether a list of valid inputs was specified for data validation. MUST be ignored if valType is not equal to 3.

C - fAllowBlank (1 bit): A bit that specifies whether the data validation treats empty or blank entries as valid.

D - fSuppressCombo (1 bit): A bit that specifies whether to suppress the display of the in-cell drop-down when the cell is selected. MUST be ignored if valType is not equal to 3.

mdImeMode (8 bits): An unsigned integer that specifies the Input Method Editor (IME) mode enforced by this data validation. This value is only used when the input language is one of the following languages:

  • Chinese Simplified (Locale identifier = 2052)

  • Chinese Traditional (Locale identifier = 1028)

  • Japanese (Locale identifier = 1041)

  • Korean (Locale identifier = 1042)

    The input for the cell can be restricted to specific sets of characters, as specified by the value of mdImeMode. MUST be a value from the following table:

    Value

    Meaning

    0x00

    No Control

    0x01

    On

    0x02

    Off (English)

    0x04

    Hiragana

    0x05

    wide katakana

    0x06

    narrow katakana

    0x07

    Full-width alphanumeric

    0x08

    Half-width alphanumeric

    0x09

    Full-width hangul

    0x0A

    Half-width hangul

E - fShowInputMsg (1 bit): A bit that specifies whether to show an input prompt when the cell is selected.

F - fShowErrorMsg (1 bit): A bit that specifies whether to display an error message when the data validation fails.

G - typOperator (4 bits): An unsigned integer that specifies the relational operator used with this data validation. If valType is equal to 0, 3 or 7, the value of the typOperator field is undefined and MUST be ignored. MUST be a value from the following table:

Value

Meaning

0x0

Between

0x1

Not Between

0x2

Equals

0x3

Not Equals

0x4

Greater Than

0x5

Less Than

0x6

Greater Than or Equal To

0x7

Less Than or Equal To

reserved (8 bits): MUST be zero, and MUST be ignored.

PromptTitle (variable):  An XLUnicodeString structure that specifies the title of the input prompt that is displayed when the cell is selected. The number of characters in this string MUST be less than or equal to 32.

ErrorTitle (variable):  An XLUnicodeString structure that specifies the title of the error that is displayed when the cell value entered fails the data validation criteria. The number of characters in this string MUST be less than or equal to 32.

Prompt (variable):  An XLUnicodeString structure that specifies the message shown in the input prompt that is displayed when the cell is selected. The number of characters in this string MUST be less than or equal to 255.

Error (variable):  An XLUnicodeString structure that specifies the error message that is displayed when the cell value entered fails the data validation criteria. The number of characters in this string MUST be less than or equal to 225.

formula1 (variable): A DVParsedFormula structure that specifies the first formula (section 2.2.2) used in data validation.

If valType is equal to 0, this formula MUST be ignored and formula1.cce MUST be zero.

If valType is not one of 0, 3, or 7 and typOperator is equal to 0 or 1, this formula is used as the lesser of two bounding values and formula1.cce MUST be greater than or equal to 1.

Otherwise, this formula is the only formula for those cases, and formula1.cce MUST be greater than or equal to 1.

formula2 (variable): A DVParsedFormula structure that specifies the second formula used in data validation.

      If typOperator is greater than or equal to 2 or valType is equal to 0, 3 or 7, this formula MUST be ignored and formula2.cce MUST be zero.

Otherwise, this formula is used as the greater of two bounding values and formula2.cce MUST be greater than or equal to 1.

sqref (variable):  A SqRefU structure that specifies all the ranges over which data validation is applied. The value of sqref.cref MUST be greater than 0 and less than or equal to 432.