Lock selected cells for editing and unlock all other cells

vilas desai
3StarLounger
Posts: 252
Joined: 16 Mar 2011, 09:33

Lock selected cells for editing and unlock all other cells

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Lock selected cells for editing and unlock all other cells

Post by 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.
Regards,
Hans

vilas desai
3StarLounger
Posts: 252
Joined: 16 Mar 2011, 09:33

Re: Lock selected cells for editing and unlock all other cells

Post by 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

User avatar
HansV
Administrator
Posts: 71240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Lock selected cells for editing and unlock all other cells

Post by 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
Regards,
Hans