Forum Discussion
Lock cell without protecting worksheet
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?
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.
- 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!!