Worksheet protection - disallow "select locked cells"

Aspose.Cells Product Family
1

When I call Worksheet.Protect(ProtectionType.All, “mypassword”, null), the worksheet allows the user to 2 things:

  1. Select locked cells
  2. Select unlocked cells.

How do I protect a worksheet so that only #2 is possible? The user should not be able to select locked cells.

2

@cometrics,

Thanks for your query.

Well, you need to make use of advanced protection options supported by Aspose.Cells APIs. See the sample code with template file(attached) for your needs for your reference:
e.g
Sample code:

     // Instantiating a Workbook object
            // Opening the Excel file through the file 
            Workbook excel = new Workbook("e:\\test2\\Bk_worksheetprotection1.xlsx");

            // Accessing the first worksheet in the Excel file
            Worksheet worksheet = excel.Worksheets[0];


            //All the worksheet cells would be locked except B3, B4 and C5 cells.
            //We need to first unlock B3, B4 and C5 cells.
            Cell cell1 = worksheet.Cells["B3"];
            Style style = cell1.GetStyle();
            style.IsLocked = false;
            cell1.SetStyle(style);


            Cell cell2 = worksheet.Cells["B4"];
            style = cell2.GetStyle();
            style.IsLocked = false;
            cell2.SetStyle(style);
            
            Cell cell3 = worksheet.Cells["C5"];
            style = cell3.GetStyle();
            style.IsLocked = false;
            cell3.SetStyle(style);


            // Restricting users to delete columns of the worksheet
            worksheet.Protection.AllowDeletingColumn = false;

            // Restricting users to delete row of the worksheet
            worksheet.Protection.AllowDeletingRow = false;

            // Restricting users to edit contents of the worksheet
            worksheet.Protection.AllowEditingContent = false;

            // Restricting users to edit objects of the worksheet
            worksheet.Protection.AllowEditingObject = false;

            // Restricting users to edit scenarios of the worksheet
            worksheet.Protection.AllowEditingScenario = false;

            // Restricting users to filter
            worksheet.Protection.AllowFiltering = false;

            // Restricting users to format cells of the worksheet
            worksheet.Protection.AllowFormattingCell = false;

            // Restricting users to format rows of the worksheet
            worksheet.Protection.AllowFormattingRow = false;

            // Restricting users to insert columns in the worksheet
            worksheet.Protection.AllowFormattingColumn = false;

            // Restricting users to insert hyperlinks in the worksheet
            worksheet.Protection.AllowInsertingHyperlink = false;

            // Restricting users to insert rows in the worksheet
            worksheet.Protection.AllowInsertingRow = false;

            // Restricting users to select locked cells of the worksheet
            worksheet.Protection.AllowSelectingLockedCell = false;

            // Allow users to select unlocked cells of the worksheet
            worksheet.Protection.AllowSelectingUnlockedCell = true;

            // Restricting users to sort
            worksheet.Protection.AllowSorting = false;

            // Restricting users to use pivot tables in the worksheet
            worksheet.Protection.AllowUsingPivotTable = false;

            worksheet.Protection.Password = "mypassword";

            // Saving the modified Excel file
            excel.Save("e:\\test2\\out1.xlsx");

Also, see the document for your further reference:

file1.zip (6.3 KB)

3

worked! Just FYI that AllowInsertingColumn was missing.

4

@cometrics,

Yes, you got it right, the sample code was just an example so it might miss some attribute(s). Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.