Cmdlet

Set-OfficeExcelValidation

Module: PSWriteOffice
Inputs: OfficeIMO.Excel.ExcelDocument
Source: SetOfficeExcelValidationCommand.cs:33 Edit on GitHub

Sets prompt and error messages on existing validation rules.

Syntax

Set-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[[-Range] <String>]
[-Sheet <OfficeSheetSelector>]
[-ShowErrorMessage]
[-ShowInputMessage]
[<CommonParameters>]

Parameter set: Range

Set-OfficeExcelValidation
[-Document <ExcelDocument>]
[-ErrorMessage <String>]
[-ErrorTitle <String>]
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-PassThru]
[-Prompt <String>]
[-PromptTitle <String>]
[-Sheet <OfficeSheetSelector>]
[-ShowErrorMessage]
[-ShowInputMessage]
[-Table <String>]
[<CommonParameters>]

Parameter set: Header

Examples

Add an input prompt

Open-OfficeExcel -Path .\Reports\Helpdesk.xlsx {
    ExcelSheet 'Tickets' {
        Set-OfficeExcelValidation -Header Priority -Table Tickets -PromptTitle 'Priority' -Prompt 'P1 is a production outage; P4 is a question.' -ShowInputMessage
    }
}

Shows a hint when someone selects a cell in the Priority column.

Reword error messages across a form

Open-OfficeExcel -Path .\Forms\Equipment-Requests.xlsx {
    ExcelSheet 'Requests' {
        Set-OfficeExcelValidation -Range E2:E500 -ErrorTitle 'Cost centre' -ErrorMessage 'Pick a cost centre from the list. Ask Finance if yours is missing.' -ShowErrorMessage
    }
}

Replaces the generic error text of the cost-centre rule in an existing form with guidance for the requester.

Parameters

-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-ErrorMessage String optional: Error message text. Omit or pass null to clear the message.
-ErrorTitle String optional: Error title. Omit or pass null to clear the title.
-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 optionalposition: 0sets: Range: 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.
-ShowErrorMessage SwitchParameter optional: Forces Excel to show the validation error.
-ShowInputMessage SwitchParameter optional: Forces Excel to show the input prompt.
-Header String requiredsets: Header: Header text identifying the target column.
-HeaderRow Int32 optionalsets: Header: One-based row containing the column headers.
-IncludeHeader SwitchParameter optionalsets: Header: Includes the header cell in the resolved column range.
-Table String optionalsets: Header: 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.