Lock cell without protecting worksheet

[Christina333]

Hi all, 

Hope everyone is doing well there. 

I would like to seek advise from experts here on how to 'lock certain cells without protecting the worksheet.' Is it possible to do so? 

My company bought in a software license and we can generate the SPC graph on the same worksheet as the raw data (side-by-side), which is really convenient.

However, in order to make this happen, the worksheet cannot be protected. But there are certain cells i need to protect to avoid the technician from simply changing the info and etc. 

Looking forward to receiving feedbacks from the experts out there. 

Thank you!

[#2]

@Christina333 

Protect individual cells in Excel
1. Start Excel.
2. Switch to the “Check” tab and select “Remove sheet protection”. You may need to allow this with a password if the worksheet is locked.
3. Select all cells by clicking in the top left corner of the table.
4. In the “Start” tab, select “Format> Format cells> Protection” and uncheck “Locked”. Click OK".
5. Now select the cells that you want to protect and choose "Format> Format cells> Protection" again. Check the box next to "Locked" and confirm with "OK".
6. Finally switch to "Check" and select "Protect sheet".

Adjust the options in the list to suit your needs.

Lock or unlock specific areas of a protected worksheet

I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :-)))

and…Please  keep asking here - I just taught myself Excel with the help of this forum.

Hope I was able to help you with this info.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@Christina333 

You could use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.

Here is what you could do:

• Right-click the sheet tab.
• Select 'View Code' from the context menu.
• Copy the code listed below into the worksheet module.
• Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
• Switch back to Excel.
• Save the workbook as a macro-enabled workbook (*.xlsm).
• Instruct users to allow macros when they open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2,C2,A4,C4"), Target) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

[#4]

@NikolinoDE 

Hey Nikoline, I will try out and see. 

Thank you so much for your feedback =))

[#5]

Hey Hans,

Lemme try out and see.
Thank you so much for your prompt response on this. =)

[#6]

I am pleased that you have received a suggested solution for your requirement.
If you found one of the suggested solutions helpful, please mark it as "Best answer" or / with a like. This will be of great benefit to other community members who read here.

Always welcome.

Wish you a nice day / night with a lot of health, joy and love.

Nikolino
I know I know nothing (Socrates)

[#7]

You absolutely did not read the question.

[#8]

@Keliz123 

You wrote in the Wrong Message and Wrong Person. As far as I can see, I've been writing to another person. If you have any problem with any answer or with myself, you can send me a private message and I will answer you for sure.

[#9]

@NikolinoDE My apologies. Absolutely no problem. First time user here so it was my mistake.

[#10]

@Keliz123 

No problem, whenever you have questions about Excel,

I am at your disposal.

By the way Welcome :)), if you will allow me, here is a link where you can get some information about this forum Welcome to your Excel discussion space!

...and how you can best ask questions to get a highly effective solution.

I wish you much success with Excel.

Nikolino

I know I don't know anything (Socrates)

[#11]

@NikolinoDE

If we want to protect cell only without protecting sheet, then what to do?

Here you are protecting sheet and my tool does not work for protected sheet. 

[#12]

@paritoshkavra 

Protect / lock individual cells in Excel

1. First of all, you should prevent Excel from locking the entire document when sheet protection is activated.
2. So first select all cells of the table with Ctrl + A or click in the top left corner of the table.
3. Right click on any cell and select "Format Cells ...". In the Protection tab, remove the checkmark next to "Locked". Click OK.

1. Now, in Excel, use the mouse to select the cells or the range that you want to protect.
2. Right-click on it and select “Format cells ...” again. In the Protection tab, check the box next to Blocked.
3. 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.

[#13]

I came by this solution through a google search, and this worked perfectly for me!
Thank you.

[#14]

@Hans Vogelaar This worked for me! I am building a financial calculator for my job and this did the trick, thanks!

[#15]

@carolineharten 

Nice to hear that!

[#16]

@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?

[#17]

@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):

1. The software won't work when the worksheet is protected and all of the cells are locked.
2. 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.
3. 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.
4. 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.

[#18]

I actually followed the steps you guys posted including #5 It works perfect for the form we are using. I appreciate the help and this will make life a little easier for my coworker who inputs the time card info and for myself with less forms needing the formulas added back when they get erased by accident! The info you guys provided to us all is excellent and helps keep alive a program they say they have "put to bed". Thanks again for the info!!

[#19]

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.

[#20]

@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?