Unable to set any property when protecting an excel worksheet using pywin32

Question

Hello,

I am using pywin32 for a project to automate a few excel files using python. In the excel file, I want to protect all the cells that contain a formula. So, I first unlock all the cells and then only lock those cells which has a formula. When I protect the worksheet with a password, I also pass all the relevant protection properties such as AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowSorting and AllowFiltering and set them to True. However, if I check what the properties are after I have protected the worksheet, they return as False.

When I am opening the file using Excel, the sheet is being protected and I am able to do edit the contents of the unlocked cells but I am unable to format, filter, or do anything else.

Relevant Information

Python version: 3.11.9

pywin32 version: 306

Excel Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20550) 64-bit

Windows: Edition Windows 11 Enterprise

Version 23H2

Installed on ‎18-‎10-‎2023

OS build 22631.4317

Experience Windows Feature Experience Pack 1000.22700.1041.0

Below is the python code for reproducibility

import win32com.client


excel_app = win32com.client.DispatchEx("Excel.Application")
excel_app.Visible = False
workbook = excel_app.Workbooks.Open("path_to_file.xlsx")

sheet = workbook.Sheets("Sheet1")

all_cells = sheet.Cells
merge_cells = sheet.Cells(1, 1).MergeArea
edited_cell = merge_cells.Cells(1, 1)
value = edited_cell.Formula if edited_cell.HasFormula else edited_cell.Value

edited_cell.Formula = "=1+1"

formula_cells = all_cells.SpecialCells(Type=-4123)  # -4213 represent xlCellTypeFormulas

all_cells.Locked = False
formula_cells.Locked = True

if isinstance(value, str) and value.startswith("="):
    edited_cell.Formula = value
else:
    edited_cell.Value = value
    merge_cells.Locked = False


sheet.Protect(
    Password="random_password",
    Contents=True,
    UserInterfaceOnly=True,
    AllowFormattingCells=True,
    AllowFormattingColumns=True,
    AllowFormattingRows=True,
    AllowInsertingColumns=True,
    AllowInsertingRows=True,
    AllowSorting=True,
    AllowFiltering=True,
)

print("AllowFormattingCells: ", sheet.Protection.AllowFormattingCells)
print("AllowFormattingColumns: ", sheet.Protection.AllowFormattingColumns)
print("AllowFormattingRows: ", sheet.Protection.AllowFormattingRows)
print("AllowInsertingColumns: ", sheet.Protection.AllowInsertingColumns)
print("AllowInsertingRows: ", sheet.Protection.AllowInsertingRows)
print("AllowSorting: ", sheet.Protection.AllowSorting)
print("AllowFiltering: ", sheet.Protection.AllowFiltering)

workbook.Save()

excel_app.Quit()