none
Protecting worksheet selectively RRS feed

  • Question

  • Hi

    I need to protect a worksheet in an xlsx selectively such that users are able to edit cells P33-P92 and U33-U92 but no other cells. How do I do that in code?

    Thanks

    Regards

    Wednesday, January 4, 2017 10:30 PM

All replies

  • Sub LockSheet()
        With Worksheets("MySheet")
            .Range("P33:P92,U33:U92").Locked = False
            .Protect Password:="secret"
        End With
    End Sub

    Change the name of the worksheet and the password.

    If you don't want to set a password, omit the part Password:="secret"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 4, 2017 11:27 PM
  • Hi Hans

    Many thanks. As I am new at this, I just found out it can't be done when cells are part of a merged range.

    I have a sheet as below where here and there (not fixed) cells are merged row wise. The sheet is generated by an application so I have no control over original layout provided. Is there a way to lock cells in a column in this case? Basically I am looking to lock cells with timings in them starting with P33/U33 going downwards and skipping the merged cells which appear on random rows.

    Thanks

    Regards



    • Edited by Y a h y a Thursday, January 5, 2017 12:39 AM
    Thursday, January 5, 2017 12:01 AM
  • Hi Y a h y a,

    Range.Locked property work with Range. so we need to provide a range.

    you can refer the below documentation for that.

    Range.Locked Property (Excel)

    you are correct that it will not work with merged cells and give you an error.

    when you merge the cells. it will only keeps upper left cell value and discard any other values of cell.

    so same thing is happened with your worksheet.

    so you can try to loop through cells and try to find merged cells and unmerge it.

    so after that you will able to use Range.locked property on that.

    With Range("a3") 
     If .MergeCells Then 
     .MergeArea.UnMerge 
     Else 
     MsgBox "not merged" 
     End If 
    End With

    please visit the below links to get more details regarding same.

    Range.UnMerge Method (Excel)

    Range.MergeArea Property (Excel)

    Range.MergeCells Property (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 5, 2017 1:35 AM
    Moderator