Lock cell without protecting worksheet

NikolinoDE
Platinum Contributor
Sep 17, 2021
paritoshkavra
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.
CELL_PROTECTION.xlsm22 KB
- Priyal835
  Copper Contributor
  Jan 23, 2023
  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?
  - NikolinoDE
    Platinum Contributor
    May 18, 2023
    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.
- phoenixdebola
  Copper Contributor
  Nov 29, 2022
  Thank you, Nikolino! Your method worked for exactly what I wanted - it protected only the cells I locked and left all others unlocked/unprotected.
  
  Instructions are reproduced below:
  
  To ONLY protect or lock individual cells in Excel without locking the whole worksheet:
  
  1. Select the whole worksheet using either 'Ctrl + A' or click in the top left corner of the sheet (little pale arrow).
  2. Right click any cell and select "Format Cells ...". In the Protection tab, remove the checkmark next to "Locked". Click OK.
  3. Now select the cells or the range that you want to protect. Right-click on it and select “Format cells ...”.
  4. In the Protection tab, check the box next to 'Locked'.
  5. Once the cells you want to lock are ticked, go to the tab and select 'Review', then 'Protect Sheet'.
  6. Ensure you tick 'Select locked cells' and 'Select unlocked cells' in the 'All users of this worksheet...' submenu.
  7. Assign a password and confirm twice.
  
  Note that the password will only be needed if unlocking the worksheet to edit the locked cells. If other unlocked cells are being manipulated, there will be no need to unlock/unprotect the worksheet.
  To summarise, unlock the whole sheet first, then lock the cells you want protected, then protect the sheet.
- teelahk32
  Copper Contributor
  Nov 03, 2022
  NikolinoDE I am curious about how it is supposed to work after following the steps and locking the chosen cells, why go on to step 5...Check tab and protect worksheet? The software they are running won't work with the worksheet protected and they stated a couple of times that the cells need to be locked due to formulae etc. Every explanation given in this thread shows protecting worksheet as the last step anyway. I ask because I have a similar issue myself. I don't want to protect the entire worksheet. I only want to lock the formula in specific cells so that they can't be erased because someone worked too fast and spaced instead of hitting enter. We need to be able to enter data in the rest of the worksheet without having to unprotect / protect the sheet every single time. Sadly there are those who don't want to have to do this step. These are time sheets we are working with and no one knows the formulas besides me, so when entering the clock ins/out somehow they are erasing the formula to calculate those hours worked.....honestly just trying to save myself a bit of fixing those same cells every pay period lol. Can the cells be locked without going back and protecting the sheet or are they just gonna have to suck it up and protect it anyway?
  - SnowMan55
    Bronze Contributor
    Nov 03, 2022
    teelahk32
    Important to remember: The words "protect" and "protected" have a general meaning, but Microsoft, in the context of Excel, has a specific meaning when referring to protecting (technical sense) a worksheet. It may be easy to confuse the two.
    
    << I don't want to protect the entire worksheet. I only want to lock the formula in specific cells... >>
    I'm certainly no expert on the protection (general sense or technical sense) of worksheets and cells. But it does not help that Excel terminology is misleading: using a "Locked" property to indicate the equivalent of having a physical lock installed, and "protect the worksheet" as the equivalent of locking all the existing physical locks (among other changes in behavior). Let me emphasize this: Protecting a worksheet (Excel technical sense for changing one property of a worksheet that affects the ability of users/software to change its contents, change the visibility of rows and columns, and other things) may cause all of its cells to be protected (general sense) from a change of content (and that's the default) ... or it may cause only some of the cells to be protected (general sense) from a change of content (if only some of the cells have a format of Locked).
    
    So regarding << The software they are running won't work with the worksheet protected >>, I have to wonder how many of these are true (multiple choice):
    The software won't work when the worksheet is protected and all of the cells are locked.
    The software won't work when the worksheet is protected and some of the cells (those which are directly used by the software) are locked.
    The software won't work when the worksheet is protected and some of the cells (none of which are directly used by the software) are locked.
    The software won't work when the worksheet is protected and none of the cells are locked.
    I can readily believe that #1 and #2 are true, but the others ...? And even for #1 and #2, there may be workarounds, such as changing the worksheet protection and or cell locking at times via VBA.
    
    << Every explanation given in this thread shows protecting worksheet as the last step >>
    No. Reread the 17 May 2021 post by Hans. The only use of "protect" in his post is in the general sense.
    
    I understand your situation if you are not willing or not allowed to use VBA in your relevant workbooks. But if you are not limited that way, you have two options for protecting (general sense) your formulas.

Forum Discussion

Resources