How to work on protected Sheet

Platinum Contributor

Sep 18, 2020

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
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
  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)
  - amitkalu
    Copper Contributor
    Sep 18, 2020
    HiNikolinoDE
    
    Thanks

Forum Discussion

Resources