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
Lock selected cells for editing and unlock all other cells
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Lock selected cells for editing and unlock all other cells
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78471
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Lock selected cells for editing and unlock all other cells
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.
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 307
- Joined: 16 Mar 2011, 09:33
Re: Lock selected cells for editing and unlock all other cells
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
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
-
- Administrator
- Posts: 78471
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Lock selected cells for editing and unlock all other cells
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
Best wishes,
Hans
Hans