Forum Discussion

amitkalu's avatar
amitkalu
Copper Contributor
Sep 18, 2020

How to work on protected Sheet

Hi,

 

I have made my excel sheet protected. But i want to edit something and re protect it back again.

 

But i dont want to redo the whole exercise of locking some selected cells again . Can u help

excel

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 18, 2020

    amitkalu 

     

    There are different ways to lock a workbook.

     

    First example-way:

    Lock or unlock specific areas of a protected worksheet

    https://support.microsoft.com/en-gb/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a?ui=en-us&rs=en-gb&ad=gb

     

    Second Examble-way:

    with VBA

    Worksheet.Protect method (Excel)

    https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect

     

    smal examble of vba:

     

    Sub nikolino_protection_sheet()
ActiveWorkbook.ActiveSheet.Unprotect ("test")

End If
ActiveWorkbook.ActiveSheet.Protect ("test")
End Sub

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • amitkalu's avatar
      amitkalu
      Copper Contributor
      Sep 18, 2020

      HiNikolinoDE 

       

      Thanks for the reply but my question is not answered. May be i will put it here in detail.

       

      Say i have protected the sheet with password. Out of 500 cells , i have protected atleast 275 cells.

       

      Now i want to edit 1(one)  protect cell as well 2 (two) unprotected cell. The problem is that i have again go through the exercise of unlocking the entire sheet and again selecting the 278 (1+2) , and locking it back again. Its painful.

       

      Needed a short cut for this.

       

      Thanks

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        Sep 18, 2020

        amitkalu 

         

        If I understand correctly, you only want to unlock 1 or 2 cells (depending on your needs) in a worksheet where something can be written / changed. The rest of the work sheet should remain blocked.
         
        If so, then:
        -----------------------------------------------------------------
        Unlock specific cells
        Step 1: mark the cells that are to be edited - right mouse button - format cells - tab protection - remove the check mark at 'locked' - ok.

        Step 2: Tools menu - Protection - Protect sheet.
        --------------------------------------------------------------
        Conversely, you can also block certain cells

        Step 1)
        Mark the entire sheet with "Ctrl" + "A"

        Step 2)
        "Format", "Cells", "Protection" Tick the "Locked" box.

        Step 3)
        Mark all cells that should be blocked, if necessary with "Ctrl"

        Step 4)
        "Format", "Cells", "Protection" Tick the "Locked" box again.

        Step 5)
        "Extras", "Protection", "Sheet"

        Finished.

        So first "unlock" everything, then select the cells to be locked, mark them and lock them.

        -------------------------------------------------------------------------------

        Not sure if this is what you are looking for, even if I hope it will help.
        As already said, without a file (without sensitive data) it is always difficult to grasp the subtleties of the question through the translation.

         

        Nikolino

        I know I don't know anything (Socrates)

Resources