Excel Object Model: Worksheet.protect/unprotect

After calling .protect on a worksheet, the worksheet's property protectContents becomes true, after calling .unprotect, the property becomes false.

Protection options

The worksheet.protect method has a number of options that control what exactly is protected.

Except for the password option, all options are variants that represent a boolean.

		Default value
`drawingObjects`	Shapes	true
`contents`	Charts and/or locked cells.	true
`scenarios`	Scenarios. Only applicable to worksheets	true
`userInterfaceOnly`	If `true`, only the user interface, but not the macros, are protected.
`allowFormattingCells`	Fomatting cells	false
`allowFormattingColumns`	Formatting columns	false
`allowFormattingRows`	Formatting Rows	false
`allowInsertingColumns`	Inserting columns	false
`allowInsertingRows`	Inserting rows	false
`allowInsertingHyperlinks`	Inserting hyperlinks.	false
`allowDeletingColumns`	Colums can be deleted if they contain no locked cell.	false
`allowDeletingRows`	Rows can be deleted if they contain no locked cell.	false
`allowSorting`	True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected.	false
`allowFiltering`	…	false
`allowUsingPivotTables`	Pivot table reports	false

Prevent the display of the message box if wrong password is provided

option explicit

const password  = "fooBarBaz42"
const sheetname = "protected sheet"

sub main() ' {
    create_workbook_with_protected_sheet

    dim wb as workbook
    dim sh as worksheet

    set wb = workbooks.open(wb_name)
    set sh = wb.sheets(sheetname)

    if try_password(sh, "password1") then
       msgBox "Unexpectedly, the wrong(?) password worked"
    end if

    if try_password(sh, password) then
       msgBox "Success: worksheet was unprotected"
    end if

end sub ' }

function wb_name() as string ' {
    wb_name  = environ$("temp") & "\protection-test.xlsx"
end function ' }

sub create_workbook_with_protected_sheet() ' {

    if dir(wb_name) <> "" then
       kill wb_name
    end if

    dim wb as workbook
    dim sh as worksheet

    set wb = workbooks.add

    set sh  = wb.worksheets.add
    sh.name = sheetname

    sh.cells(1,1) = 42
    sh.cells(2,1) ="Hello world"

    sh.protect password

    wb.saveAs                           _
       fileName   := wb_name          , _
       fileFormat := xlOpenXMLWorkbook

    wb.close

end sub ' }


function try_password(sh as worksheet, pw as string) as boolean ' {

    on error resume next

    sh.unprotect pw

    if err.number = 1004 then ' {
    '
    '  Sheet could not be unprotected
    '
       try_password = false
       exit function

    end if ' }

    try_password = true

end function ' }

Github repository about-MS-Office-object-model, path: /Excel/Worksheet/protect-unprotect/main.vb

Excel Object Model: Worksheet.protect/unprotect

Protection options

Prevent the display of the message box if wrong password is provided

See also