C# excel hide and lock rows

Question

Recently I have been converting a project from VBA to C# but I have run across an issue with .Hidden(), .Locked(), and .Protect()

In the VBA implementation if I hide(rows)->lock->protect then I cannot unhide the rows (as intended), but in the C# implementation if I hide(rows)->lock->protect the rows can be unhidden (highlight rows, right click, unhide)

Is there something I am missing, or is there a different way that the C# version needs to be written to produce the same result (rows cannot be unhidden) as the VBA version?

I have simplified the code to these short snippets that reproduce the results. Both versions create a new workbook, modify a cell, hide-lock-protect rows, and save/close the workbook.

C# version:

using Excel = Microsoft.Office.Interop.Excel;
...

private void button1_Click(object sender, EventArgs e)
{

    Excel.Application ex = new Excel.Application();
    Excel.Workbooks Books = ex.Workbooks;


    //create and save the output workbook (so only .save() needs to be called later)
    Excel.Workbook OutputBook = Books.Add();
    OutputBook.SaveAs("C:\\TestingFolder\\Outputbook.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);

    //write secret stuff
    OutputBook.Sheets[1].Cells[15,15] = "Stuff";

    //hide and lock rows around secret stuff
    OutputBook.Sheets[1].Range["10:20"].EntireRow.Hidden = true;
    OutputBook.Sheets[1].Range["10:20"].EntireRow.Locked = true;


    //protect the sheet with a bad password
    OutputBook.Sheets[1].Protect(
                                    "SomePassword123",//password
                                    false,  //drawing objects
                                    true,   //Contents
                                    false,  //scenarios
                                    false,  //user interface
                                    true,   //format cells
                                    true,   //format columns
                                    true,   //format rows
                                    false,  //insert columns
                                    false,  //insert rows
                                    true,   //insert hyperlinks
                                    false,  //delete columns
                                    false,  //delete rows
                                    true,   //allow sorting
                                    true,   //allow filtering
                                    true    //allow pivot tables
                                );


    //save and close output workbook
    OutputBook.Save();
    OutputBook.Close(false);


    //-----general cleanup start-----
    Books.Close();
    ex.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(OutputBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(Books);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);

    OutputBook = null;
    Books = null;
    ex = null;

    GC.Collect();
    //-----general cleanup end-----


    //show message that the task completed
    MessageBox.Show("done");

}

and the VBA version:

Private Sub CommandButton1_Click()

    'create and save the output workbook (so only .save() needs to be called later)
    Dim OutputBook As Workbook
    Set OutputBook = Workbooks.Add
    Call OutputBook.SaveAs("C:\TestingFolder\Outputbook.xlsm", ThisWorkbook.FileFormat)

    'write secret stuff
    OutputBook.Sheets(1).Cells(15, 15) = "Stuff"

    'hide and lock rows around secret stuff
    OutputBook.Sheets(1).Range("10:20").EntireRow.Hidden = True
    OutputBook.Sheets(1).Range("10:20").EntireRow.Locked = True

    'protect the sheet with a bad password
    OutputBook.Sheets(1).Protect Password:="SomePassword123", _
                                DrawingObjects:=False, _
                                Contents:=True, _
                                Scenarios:=False, _
                                AllowFormattingCells:=True, _
                                AllowInsertingHyperlinks:=True, _
                                AllowSorting:=True, _
                                AllowFiltering:=True, _
                                AllowUsingPivotTables:=True

    'save and close output workbook
    Call OutputBook.Save
    Call OutputBook.Close

    'show message that the task completed
    MsgBox "done"

End Sub

Answer 1

In your Protect method, the format rows argument must be set to false and not true.