How to make an excel cell ReadOnly using C#?

Question

Range range= (Range)this.workSheet.Cells[1,1];
range.AllowEdit = false;

When I set the AllowEdit property to false, a compile error will show:

Error: Property or indexer 'Microsoft.Office.Interop.Excel.Range.AllowEdit' cannot be assigned to -- it is read only

How can I set the range of cells to be Read Only?

When I am using validation for this range I got Some exception on my CellContentChanged Event .

Here is the code which is in CellContentChanged:

   var updater = new Action<StyleColorItem>(
           item =>
           {
              var editedItem = _taskViewModel.TrackedItems.First(it => it.Id == item.Id); 
            // Above line I am getting the exception like "Sequence contains no matching element"
               editedItem.Update(item);'
           });

Solution 1

There is no way to make a cell read-only in excel.

What you can do in your c# code is, define a cell that is going to be "read-only" in a variable or a list, subscribe to the SheetChange event , in the SheetChange event, if that cell gets changed, simply undo that change.

Example private List readOnlyCells = new List();

private void OnActiveSheetCellChange(object changedSheet, Excel.Range changedCell)
{
  if (readOnlyCells.Contains(changedCell))
      changedCell.Value = string.Empty;
      //.... YOUR CODE

Update

Another alternative is to use Data Validation:

changedCell.Validation.Add(Excel.XlDVType.xlValidateCustom, Type.Missing, Type.Missing, "\"\"");

Using it you will have less control and the cell will simply not accept any input.

Solution 2

I think it can be done by setting the Locked property to true, and protecting the worksheet.

range.Locked = true;
this.workSheet.Protect(Type.Missing, Type.Missing,
                       true,
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                       Type.Missing);