Forum Discussion
Lock cell without protecting worksheet
Protect / lock individual cells in Excel
- First of all, you should prevent Excel from locking the entire document when sheet protection is activated.
- So first select all cells of the table with Ctrl + A or click in the top left corner of the table.
- Right click on any cell and select "Format Cells ...". In the Protection tab, remove the checkmark next to "Locked". Click OK.
- Now, in Excel, use the mouse to select the cells or the range that you want to protect.
- Right-click on it and select “Format cells ...” again. In the Protection tab, check the box next to Blocked.
- Select the “Check” menu in Excel and click the “Protect sheet” button. Assign a password and confirm this twice.
Do not forget!
In "All users of this worksheet may:" submenu
The checkmarks for “Select locked cells” and “Select unlocked cells” should be ticked.
You can also do it with VBA code.
Sub CellProtect()
Dim Blatt As Worksheet, rng As Range
Set Blatt = Worksheets("Sheet1")
Set rng = Blatt.Range(Cells(1, 1), Cells(1, 1))
rng.Select
Blatt.Unprotect
Blatt.Cells.Locked = False
rng.Locked = True
Blatt.Protect
End Sub
Example file is inserted with VBA code
Additional Infos:
Lock or unlock specific areas of a protected worksheet
I would be happy to know if I could help.
NikolinoDE
Was the answer useful? Mark them as helpful!
This will help all forum participants.
NikolinoDE Thank you very much for sharing this..
Along with protecting/Locking the cell, I also want to protect and restrict the "check box" added in that locked cells.
Can you please guide me for the same?
- Here are the steps to protect and restrict checkboxes added to locked cells in Excel:
1. Select the cells where you want to insert the checkboxes.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. Go to the "Protection" tab in the "Format Cells" dialog box.
4. Uncheck the checkbox next to "Locked." This will unlock the cells for protection.
5. Click "OK" to close the dialog box.
6. Select the cells again and right-click. This time, choose "Protect Cells" from the context menu.
7. In the "Protect Cells" dialog box, check the box next to "Select locked cells."
8. Optionally, enter a password to prevent the protection from being removed.
9. Click "OK" to close the dialog box and protect the cells.
After following these steps, the cells will be protected, and users will not be able to modify the checkboxes in the locked cells.
Please note that these instructions are based on Excel for Windows. The steps may vary slightly in other versions of Excel or in Excel for Mac, but the general process should be similar. Assume checkbox means an Activex control.
Private Sub Worksheet_Change(ByVal Target As Range) Dim Condition As Byte If Target.Text <> "" Then Condition = 1 If Not Intersect(Target, Range("$A$7:$H$7")) Is Nothing Then ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = Condition 'Check box End If If Not Intersect(Target, Range("$F$8:$I$8")) Is Nothing Then ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = Condition 'Check box End If If Not Intersect(Target, Range("$F$9:$G$9")) Is Nothing Then ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = Condition 'Check box End If End SubIf it's not an Aktice X control, it won't work, after all, these controls are there to make specific entries in protected spreadsheets despite protection.
Otherwise it would not be possible to create any meaningful applications that users without Excel knowledge could use without hesitation :).