Cmdlet

Add-OfficeExcelValidation

Aliases: ExcelValidation

Module: PSWriteOffice
Aliases: ExcelValidation
Inputs: OfficeIMO.Excel.ExcelDocument
Source: AddOfficeExcelValidationCommand.cs:43 Edit on GitHub

Adds a data validation rule to a range or a header column.

Syntax

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
[-NoBlank]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Range] <String>
[-Sheet <OfficeSheetSelector>]
[-Type] <ExcelValidationType>
[-Values] <String[]>
[<CommonParameters>]

Parameter set: ListRange

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
[-NoBlank]
-Operator <ExcelDataValidationOperator>
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Range] <String>
[-SecondValue <Object>]
[-Sheet <OfficeSheetSelector>]
[-Type] <ExcelValidationType>
[-Value] <Object>
[<CommonParameters>]

Parameter set: CompareRange

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
[-Formula] <String>
[-NoBlank]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Range] <String>
[-Sheet <OfficeSheetSelector>]
[-Type] <ExcelValidationType>
[<CommonParameters>]

Parameter set: FormulaRange

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-NoBlank]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Sheet <OfficeSheetSelector>]
[-Table <String>]
[-Type] <ExcelValidationType>
[-Values] <String[]>
[<CommonParameters>]

Parameter set: ListHeader

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-NoBlank]
-Operator <ExcelDataValidationOperator>
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-SecondValue <Object>]
[-Sheet <OfficeSheetSelector>]
[-Table <String>]
[-Type] <ExcelValidationType>
[-Value] <Object>
[<CommonParameters>]

Parameter set: CompareHeader

Add-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
[-Formula] <String>
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-NoBlank]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Sheet <OfficeSheetSelector>]
[-Table <String>]
[-Type] <ExcelValidationType>
[<CommonParameters>]

Parameter set: FormulaHeader

Examples

Restrict a column to a list

Open-OfficeExcel -Path .\Reports\Helpdesk.xlsx {
    ExcelSheet 'Tickets' {
        ExcelValidation -Range C2:C500 -Type List -Values 'Open', 'In progress', 'On hold', 'Closed'
    }
}

Adds a drop-down that allows only the listed statuses in C2:C500.

Validate quantities in a request form

Open-OfficeExcel -Path .\Forms\Equipment-Requests.xlsx {
    ExcelSheet 'Requests' {
        ExcelValidation -Header Quantity -Table Requests -Type WholeNumber -Operator Between -Value 1 -SecondValue 500 -NoBlank -ErrorTitle 'Quantity' -ErrorMessage 'Enter a whole number from 1 to 500.'
    }
}

Allows whole numbers from 1 to 500 in the Quantity column of the Requests table and explains the rule when a wrong value is typed.

Validate with a formula

Open-OfficeExcel -Path .\Projects\Plan.xlsx {
    ExcelSheet 'Tasks' {
        ExcelValidation -Range D2:D300 -Type Formula -Formula '=D2>=C2' -ErrorMessage 'The end date must be on or after the start date.'
    }
}

Rejects end dates that come before the start date in the same row.

Parameters

-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-ErrorMessage String optional: Error message shown to the user.
-ErrorTitle String optional: Error title shown to the user.
-NoBlank SwitchParameter optional: Rejects blank input in the validation rule.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Prompt String optional: Input prompt text. Omit or pass null to clear the prompt.
-PromptTitle String optional: Input prompt title. Omit or pass null to clear the title.
-Range String requiredposition: 0sets: ListRange, CompareRange, FormulaRange: A1 cell range on the selected worksheet.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-Type ExcelValidationType requiredposition: 1: Shared chart family. Bubble requires the separate native bubble-data contract.; Possible values: List, WholeNumber, Decimal, Date, Time, TextLength, Formula
-Values String[] requiredposition: 2sets: ListRange, ListHeader: Allowed values for the dropdown list.
-Operator ExcelDataValidationOperator requiredsets: CompareRange, CompareHeader: Validation operator.; Possible values: Between, NotBetween, Equal, NotEqual, LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual
-SecondValue Object optionalsets: CompareRange, CompareHeader: Upper bound or second formula used by between comparisons.
-Value Object requiredposition: 2sets: CompareRange, CompareHeader: Value or values consumed by the selected operation.
-Formula String requiredposition: 2sets: FormulaRange, FormulaHeader: Validation formula.
-Header String requiredsets: ListHeader, CompareHeader, FormulaHeader: Header text identifying the target column.
-HeaderRow Int32 optionalsets: ListHeader, CompareHeader, FormulaHeader: One-based row containing the column headers.
-IncludeHeader SwitchParameter optionalsets: ListHeader, CompareHeader, FormulaHeader: Includes the header cell in the resolved column range.
-Table String optionalsets: ListHeader, CompareHeader, FormulaHeader: Native Excel table used to resolve the source range or header.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.