Cmdlet

Protect-OfficeExcelSheet

Module: PSWriteOffice
Inputs: OfficeIMO.Excel.ExcelDocument
Source: ProtectOfficeExcelSheetCommand.cs:39 Edit on GitHub

Protects a worksheet and chooses what users may still do.

Syntax

Protect-OfficeExcelSheet
[-AllowAutoFilter]
[-AllowDeleteColumns]
[-AllowDeleteRows]
[-AllowFormatCells]
[-AllowFormatColumns]
[-AllowFormatRows]
[-AllowInsertColumns]
[-AllowInsertHyperlinks]
[-AllowInsertRows]
[-AllowPivotTables]
[-AllowSort]
[-AllowTableEditing]
[-Document <ExcelDocument>]
[-NoSelectLockedCells]
[-NoSelectUnlockedCells]
[-PassThru]
[-Password <SecureString>]
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: Default

Examples

Protect a sheet

Open-OfficeExcel -Path .\Finance\Pricing-Model.xlsx {
    Protect-OfficeExcelSheet -Sheet Inputs
}

Locks the Inputs sheet so only unlocked cells can be edited.

Protect a shared list but keep it usable

Open-OfficeExcel -Path .\HR\Staff-Directory.xlsx {
    ExcelSheet 'Directory' {
        Protect-OfficeExcelSheet -AllowSort -AllowAutoFilter -AllowFormatColumns
    }
}

Protects the staff list while still letting colleagues sort, filter and resize columns.

Protect a sheet with a password

$password = Read-Host -Prompt 'Sheet password' -AsSecureString
Open-OfficeExcel -Path .\Finance\Pricing-Model.xlsx {
    Protect-OfficeExcelSheet -Sheet Inputs -Password $password -AllowSort -AllowAutoFilter
}

Locks the input sheet with a password read from the console, still allowing sorting and filtering.

Parameters

-AllowAutoFilter SwitchParameter optional: Allow AutoFilter.
-AllowDeleteColumns SwitchParameter optional: Allow deleting columns.
-AllowDeleteRows SwitchParameter optional: Allow deleting rows.
-AllowFormatCells SwitchParameter optional: Allow formatting cells.
-AllowFormatColumns SwitchParameter optional: Allow formatting columns.
-AllowFormatRows SwitchParameter optional: Allow formatting rows.
-AllowInsertColumns SwitchParameter optional: Allow inserting columns.
-AllowInsertHyperlinks SwitchParameter optional: Allow inserting hyperlinks.
-AllowInsertRows SwitchParameter optional: Allow inserting rows.
-AllowPivotTables SwitchParameter optional: Allow PivotTables.
-AllowSort SwitchParameter optional: Allow sorting.
-AllowTableEditing SwitchParameter optional: Allow common protected-table workflows: selecting cells, inserting rows, sorting, and filtering.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-NoSelectLockedCells SwitchParameter optional: Prevents selecting locked cells while the worksheet is protected.
-NoSelectUnlockedCells SwitchParameter optional: Prevents selecting unlocked cells while the worksheet is protected.
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Password SecureString optional: Password used to open an encrypted workbook package.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.

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.