How to work on protected Sheet

Copper Contributor

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

5 Replies

@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-75...

 

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.

Hi@NikolinoDE 

 

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

@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)

Hi@NikolinoDE 

 

Thanks 

You are welcome.
I am happy that I can help you.

Nikolino
I know I don't know anything (Socrates)