none
How to make a excel cell ReadOnly by C# code?

    Question

  • Hi,

    I'm using VSTO to develop an Excel 2003 addin using C# programming language in Visual Studio 2008.

    In my code I want to set a cell to be Read Only, so that users can't modify the value of that cell.But I cannot find a property to set.See below code:

     

    // first get a cell A1
    
    Range temp = (Range)this .workSheet.Cells[1,1];

    // then make it ReadOnly
    // The only property I find is AllowEdit, but the next line won't compile.
    // Error: ErrorProperty or indexer 'Microsoft.Office.Interop.Excel.Range.AllowEdit' cannot be assigned to -- it is read only
    temp.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

    Question: How can I set the cell to be Read Only?

    Thanks in advance

     

    Thursday, July 22, 2010 9:56 AM

Answers

  • Hi,

    In order to lock the cells for a given range first set the Locked property for that range to true and then protect that particular sheet. This will lock the cells from being edited. PFB a sample code for the same. It will lock the cell range from A1 to C3 from editing. 

    this.Application.Cells.Locked = false;
          this.Application.get_Range("A1", "C3").Locked = true;
          Excel.Worksheet Sheet1 = (Excel.Worksheet)this.Application.ActiveSheet;
          Sheet1.Protect(missing, missing, missing, missing, missing, missing, missing, 
            missing, missing, missing, missing, missing, missing, missing, missing, missing);
    

    [Please mark the post as answer if it answers your question]

    Thanks & Regards,

    Sai Pavan

    • Marked as answer by zigangwang Friday, July 23, 2010 9:20 AM
    Thursday, July 22, 2010 12:34 PM

All replies

  • BTW, when I lanuch my thread(above article) I use "Insert Code Block" button to insert my c# code,and it looks nifty after I click "Submit".

    But, after I edit my thread above and click 'Submit', then the code is in a mess. It's weird because I didn't edit the code block at all!

    Then I try to edit again to make the code looks nifty as the first time. I delete the extra spaces and line breaks in code block,but it doesn't work and is still in a mess.

    I think this may be a bug. Has anyone encounterd the same problem?

    Thursday, July 22, 2010 10:08 AM
  • Protection of ranges is managed by first unlocking cells, and then protecting the worksheet.

    this is a kb on how to do it manually.

    Thursday, July 22, 2010 12:01 PM
  • Hi,

    In order to lock the cells for a given range first set the Locked property for that range to true and then protect that particular sheet. This will lock the cells from being edited. PFB a sample code for the same. It will lock the cell range from A1 to C3 from editing. 

    this.Application.Cells.Locked = false;
          this.Application.get_Range("A1", "C3").Locked = true;
          Excel.Worksheet Sheet1 = (Excel.Worksheet)this.Application.ActiveSheet;
          Sheet1.Protect(missing, missing, missing, missing, missing, missing, missing, 
            missing, missing, missing, missing, missing, missing, missing, missing, missing);
    

    [Please mark the post as answer if it answers your question]

    Thanks & Regards,

    Sai Pavan

    • Marked as answer by zigangwang Friday, July 23, 2010 9:20 AM
    Thursday, July 22, 2010 12:34 PM
  • Hi Sai Pavan Viswanath,

    I've tried your code but the Protect() method will cause all the cells in the sheet locked, but I want to lock just one cell, not all the cells.

    Is there any soluction?

     

    Thursday, July 22, 2010 4:02 PM
  • Hi zigangwang,

    I implemented the solution on my machine before posting the code. The first line of code will unlock all the cells and after that I defined a range and locked it. It worked fine for me.

     

    Please look at the post in the following link. It may help solve your issue.

     

    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/583f39a1-c3e2-4aaf-8c85-caacb6dbfa94

     

    [Please mark the post as answer if it answers your question]

    Thanks & Regards,

    Sai Pavan

    Thursday, July 22, 2010 5:14 PM
  • Hi Sai Pavan Viswanath,

    You code now works on my laptop. It turns out that I make a mistake when I write the first line of your code in my program.

    this
    .Application.Cells.Locked = false
    ;

    My addin is an Application-level addin and I shall write code like this:

    Globals.ThisAddIn.Application.Cells.Locked = false;
    
    

    Thank you for your help!

    Regards,

    Zigang Wang

    Friday, July 23, 2010 9:19 AM
  • Hi ,

    Can anybody suggest me that is there any other way to make columns readonly because i don't want to protect my sheet otherwise it effects others features like i am also creating group of columns,

    Thanks,

    Amit

    Friday, November 30, 2012 12:38 AM
  • i need lock particular column ... Excel data must be yo database data ... in that if 3 columns there means one column should be read only rest of them editable ... 
    Plzzz help ...
    Thanks in advance...
    Monday, June 24, 2013 10:43 AM