Lock selected cells for editing and unlock all other cells

[vilas desai]

Hello Experts,

In the attached file, I am looking to block for editing those cells which have text / characters and unblock all other cells.

Thanks and best regards
Vilas Desai

[HansV]

All cells are locked, but this only becomes effective when you protect the sheet.
So you have to perform two steps:

Step 1: Unlock cells.
Press F5 or Ctrl+G to activate the Go To dialog.
Click Special...
Select Blanks, then click OK.
Press Ctrl+1 to activate the Format Cells dialog.
Activate the Protection tab.
Clear the Locked check box, then click OK.

Step 2: Protect the sheet
On the Review tab of the ribbon, click Protect Sheet.
Use the check boxes to specify what the user will be allowed to do.
At the very least, leave the check box 'Select unprotected cells' ticked. Otherwise, the user won't be able to edit unlocked cells.
If you want to specify a password that will be needed to unprotect the sheet later on, enter it in the box. Do not forget it!
Click OK. If you provided a password, you'll have to re-enter it as confirmation.

Finally, save the workbook.

[vilas desai]

Thanks a lot Hans.
If I have multiple sheets in the same workbook, should I repect this for all the ws or is there some way to do it in a macro or by selecting all sheets?
Thanks and best regards
Vilas Desai

[HansV]

Try this macro. Let me know if it causes problems.

Code: Select all

Sub UnlockAndProtect()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
        wsh.Cells.SpecialCells(xlCellTypeBlanks).Locked = False
        wsh.Protect Password:="secret", Contents:=True
    Next wsh
End Sub