C# Excel Add in : Make worksheet read only but editable through code

Question

I have created an Excel add-in in C#, In my add-in I have a form that retrieves data from web service and writes it to excel, I want to make the excel sheet read only, but I also want to edit it from behind the code.

What i want to do:

Basically I want to block user from accessing the worksheet, he can only make changes through the form that I have created.

I tried:

1- To add cell validation to forbid any changes to the cell but it still allows other operations on sheet such as delete rows columns etc.

2- To protect worksheet using a password like this.

var activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
activeWorksheet.Protect("password");

But the above will not let me edit the worksheet through code, I have to unlock the worksheet whenever i want to make changes ie.

activeWorksheet.Unprotect("password");
// Do some changes.
activeWorksheet.Protect("password");

This way worksheet will stay unprotected while my Edit code is running (it may consume some time as there may be thousands or more records in sheet).

I can think of one way is to unlock invidual cells to edit them and lock them again, I tried the below code but its not working:

var cellToEdit = activeWorksheet.Range["A2"];
activeWorksheet.Protection.AllowEditRanges.Add("A1",cellToEdit , "password");
cellToEdit.value = "changed cell";

Please suggest me some solution.

Answer 1

I have tested writing a code than runs longer say 3 to 4 seconds and it seems that by default, excel doesnt allow worksheet to be edited while the code in still running.