none
Read only Row in VSTO Excel Doc level plugin RRS feed

  • Question

  • Formatting certain cells to read only

    Posted by CJ on January 04, 2002 6:18 AM
    Is there a way of locking certain cells in a spreadsheet so as they cannot be altered and they are read only?
    Thanx :o)


    Re: Formatting certain cells to read only

    Posted by Scott on January 04, 2002 6:35 AM
    If you format the cells as locked Format-Cells and then on the Protection Tab, select "Locked". Then when you protect the sheet: Tools-Protection-Protect Sheet, These cells will be locked. You may want to make sure any cells that you want the user to have access to are NOT locked when you protect the sheet.

    Re: Formatting certain cells to read only

    Posted by lenze on January 04, 2002 6:44 AM
    Another way you can do this is through Data Validation. After you have a cell populated with the desired contents, Choose Data Validation and use the Custom option. For the formula enter =""
    This will prevent someone from entering data in the cell and does not require the sheet to be protected. This can be particularly useful if you have macros that require the sheet to be unprotected to run

    lenze


    Hi Sir, 

    From the above information I did codding like as below it is giving error.

    is there any solutions I know error is in parameter I am not able to figure it Formatting certain cells to read only

    Posted by CJ on January 04, 2002 6:18 AM
    Is there a way of locking certain cells in a spreadsheet so as they cannot be altered and they are read only?
    Thanx :o)


    Re: Formatting certain cells to read only

    Posted by Scott on January 04, 2002 6:35 AM
    If you format the cells as locked Format-Cells and then on the Protection Tab, select "Locked". Then when you protect the sheet: Tools-Protection-Protect Sheet, These cells will be locked. You may want to make sure any cells that you want the user to have access to are NOT locked when you protect the sheet.

    Re: Formatting certain cells to read only

    Posted by lenze on January 04, 2002 6:44 AM
    Another way you can do this is through Data Validation. After you have a cell populated with the desired contents, Choose Data Validation and use the Custom option. For the formula enter =""
    This will prevent someone from entering data in the cell and does not require the sheet to be protected. This can be particularly useful if you have macros that require the sheet to be unprotected to run

    lenze



    Hi Sir, 

    From the above information I did codding like as below it is giving error.

    is there any solutions I know error is in parameter I am not able to figure it out. I want to make ranges read only.

    ReadonlyRange is my Range object here in this case

    ReadonlyRange.Validation.Add(Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, missing ,"", missing);
    . I want to make ranges read only.

    ReadonlyRange is my Range object here in this case

    ReadonlyRange.Validation.Add(Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, missing ,"", missing);

    Wednesday, February 11, 2009 10:56 AM

Answers

  • Hello,

    Do not use the Validation approach. Even we cannot edit it, we still can copy values from other cells into it.

    To achieve your objective, just set the cells you want readonly to locked status and call the Worksheet's protect method. See the following codes that work for me,

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


    Thanks,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Manish_Agravat Monday, February 16, 2009 11:12 AM
    Wednesday, February 11, 2009 11:35 AM
    Moderator
  • OK.

    Try the following codes,

    Excel.Range range = this.Application.get_Range("A1", missing);
    range.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, missing, "=\"\"", missing);


    Warning,

    This kind of protection does not make sense to me. Yes, I cannot type to modify the A1 cell now. But I can copy B1's value and paste into A1.



    Regards,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Manish_Agravat Monday, February 16, 2009 11:12 AM
    Wednesday, February 11, 2009 1:09 PM
    Moderator

All replies

  • Hello,

    Do not use the Validation approach. Even we cannot edit it, we still can copy values from other cells into it.

    To achieve your objective, just set the cells you want readonly to locked status and call the Worksheet's protect method. See the following codes that work for me,

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


    Thanks,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Manish_Agravat Monday, February 16, 2009 11:12 AM
    Wednesday, February 11, 2009 11:35 AM
    Moderator
  • What I want is to make it not editable. 

    still one can copy it but cannot change it. 


    protection is creating problem with 

    Cells.ClearOutline() ; 

    when we run this method for say 5000 cells it create problem with performance.
    and I don’t want to go for protection and passwords  

    Wednesday, February 11, 2009 11:46 AM
  • Hi,

    I think we have a misunderstanding here. I do not mean that the user can copy the protected cell's value. I mean the user can copy values from other cells and paste it into the protected cell. You can have a test to prove this behavior in your side. So, based on your description, I am pretty sure that the Validation is not sufficient for your protection job.

    As far as I know, the only way is Range.Locked and Worksheet.Protect. As to your concerning about the performance issue, why not Unprotect the sheet and then call Cells.ClearOutline. After that, we can protect the sheet again by codes. All these will be executed in codes and the users still cannot edit the protected cells. Does that make sense for you?


    Thanks,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    Wednesday, February 11, 2009 12:00 PM
    Moderator
  • Again thanks but what i want is as below


    select cells 
    Choose Data Validation and use the Custom option. For the formula enter =""


    it will allow you to type but when you press enter and go to non edit mode it will prompt for error 



    Please tell me how to run that method with the formula ="" using custom validation 

    please please please 



    Wednesday, February 11, 2009 12:06 PM
  • OK.

    Try the following codes,

    Excel.Range range = this.Application.get_Range("A1", missing);
    range.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, missing, "=\"\"", missing);


    Warning,

    This kind of protection does not make sense to me. Yes, I cannot type to modify the A1 cell now. But I can copy B1's value and paste into A1.



    Regards,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Manish_Agravat Monday, February 16, 2009 11:12 AM
    Wednesday, February 11, 2009 1:09 PM
    Moderator

  • Its cool 


    thanks  :) 

    yooooooooooooooooooo problem solved  

    Wednesday, February 11, 2009 1:38 PM
  • Hi Ji.Zhou,

    I would like to ask one more thing.

    In excel cells i am in edit mode at that time i want to desable my own created ribbon buttons.  and when i will come out from the edit mode it should be enable it is possible?  


    In Excel 2007 it is happening with the default ribbon opetions. 

    why i want to desable because when i am clicking that button during the edit mode it is giving 

     =SheetImport!D5 "=my sheet name ! D5" range so i want office default ribbon control type functionality



    Thursday, February 12, 2009 1:36 PM
  • Hi,

    As far as I know, there is no event can catch the entering or existing edit mode in Excel.

    As a workaround, may I suggest before we do something in our ribbon control's click event handle, we first proactively test if Excel is in edit mode via reading Application.Ready. If yes, our codes do the logical process. If no, our codes do nothing.

    Please read more information in,
    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/3333e18b-cef3-4d78-b47a-6916a1b2d84c/


    And this follow up question does not belong to VSTO.


    Thanks,
    Ji Zhou
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    Tuesday, February 17, 2009 2:26 AM
    Moderator
  • Thank you
    Wednesday, May 6, 2009 9:17 AM
  • hi,

    I tried the

    this.Cells.Locked = false; and only locking the cells I need then calling .Protect(), however since I have grouping in my sheet, and they start collapsed, the minute I try to open them, it does not allow me to. It complains about the sheet having to be unlocked first. I tried playing with the various options for the Protect method, but none seem to fix this issue. Please advice.

    Thanks

    Iris


    Iris Abramovitz

    Wednesday, August 31, 2016 3:06 PM