[Solved] Can calc lock cells to prevent editing or deletion?

[MissingBlink399]

Does calc have the ability to lock out individual cells or a range of cells so that the cells are essentially protected against alteration or deletion?

[Zizi64]

Hi,

Protect cell in "Format - Cells - Cell protection (check or uncheck for)

and then:
Tools - Protect - Sheet... (with or without password!)

[MissingBlink399]

Hi,

Protect cell in "Format - Cells - Cell protection (check or uncheck for)

and then:
Tools - Protect - Sheet... (with or without password!)

It works great!

[scruffyeagle]

This solution didn't work. I've made a budgeting spreadsheet that has a couple of lists of entries. (I've attached a copy of my spreadsheet with this post.) Locking the entire sheet prevents editing the values and labels! That makes it useless as a tool for managing continuously changing data. All I want to do is lock selected cells.

I want to be able to give a copy of this spreadsheet to my mother for her use, but it's more likely than not that she'll accidentally modify or delete the formulas that make the sheet useful.

Explanation of sheet:
The 1st & 2nd areas are the work areas of the spreadsheet. The 1stt area is set monthly expenses that don't change, such as "budget plan" utilities payment. The 2nd area is optional expenses that will change from month to month, depending on what I need to buy. All items in both areas are entered as negative numbers. It sums the first area so the user can see what the current amount available for spending is, then does a secondary sum that adds on the items of the 2nd area. The 3rd area is unused except at the start of a new month. At start of month, the data from the 3rd area is copied enmasse to the 1st area to reset the work area for use. I want the user to be able to change both labels and amounts of items at will. But - I want the user prevented from modifying the contents of the cells which do the calculations, and the label cells next to those cells.

[Linr]

You should only "Protected" cells that you don't want edited. It looks like you protected even the fields you want edited. Also, you will have to protect each sheet separately. Protecting the document is not related to what you are looking for.

Unless our versions are incompatible, I've unprotected all the $ fields that don't have a (=) sign.

[scruffyeagle]

Yes, this was an improvement. But, the item labels still aren't accessible for users to modify on an as-needed basis. How did you unprotect the fields?

In protecting this, I simply did what the response post here said to do. I did

"Protect cell in "Format - Cells - Cell protection (check or uncheck for)"
and then did:
"Tools - Protect - Sheet" without password.

When protecting cells, there's a blurb next to where you do the checkmark for protecting the cell, in the format options dialogue box that says:
"Cell protection is only effective after the current sheet has been protected."
followed by:
"Select 'Protect Document' from the 'Tools' menu, and specify 'sheet'."

So, regardless of getting instruction from here or from within the program, I was following instructions. And, it didn't work. I wasn't able to only protect selected cells from being modified. Protection doesn't seem to work at all unless the entire sheet gets made unmodifiable. In which case, why even bother having a control for protecting (or not) particular cells? But, I think the key to resolving this is in the method you used for unprotecting only certain cells. How did you do that?

[scruffyeagle]

Never mind... I figured it out. I got to thinking about it after I posted here, and did some experimenting. Now, I'm going to post a clear explanation re. what I've figured out, for the others who've struggled with this issue.

*) It's NOT possible to apply protection to only selected cells. The protection mechanism only functions by addressing the cells enmasse. This could be the collection of cells which are in the current sheet, or all the cells in the entire workbook (document).

*) What IS possible, is to exempt selected cells from being protected along with all the rest when the protection mechanism is applied. This is accomplished by
1) Menu-click to access "Format cells"
2) Select "Cell Protection" tab
3) Remove checkmark next to the label "Protected"

Having done that, the cell whose properties you've tweaked by removing the checkmark will be exempt from being protected when the sheet (or the entire document) is protected.

*) It's worth mention that I discovered a shortcut for doing a bunch of cells all at once. After tweaking the properties of a given cell, you can select it along with several other cells (in a column, for example) and use the "Fill" function from the "Edit" menu to copy the initial cell's value into the rest. The initial cell's properties get copied along with the data, and that includes the status of that checkmark next to the label "Protected".

I'm attaching a copy of my updated version of the budgeting spreadsheet with this post. If you check the protection status of the sheet via "Tools --> Protect Document", you'll find that "Sheet" has a checkmark next to it. If you remove that checkmark, then all the cells of the sheet will be subject to editing both values & properties. But, even with the sheet protected, the fields I exempted from protection are now available for use. I've removed the "Instructions" sheet, since I'll need to re-write that for the sake of my mother's usage, but the budgeting sheet is improved. Now, the label fields in the "Current Budgeting" area are automatically updated whenever corresponding fields in the "Full month of expenses" area are changed.

I hope some of you find what I've written to be helpful!

[Colin Hunter]

I had the same problem and eventually found the solution to be very simple.

All cells are protected by default so when you apply protection to the sheet, all cells are protected.

To protect specific cells, first simply select the entire sheet (click top left button above row numbers), and Format > Cells and deselect Protect to unprotect every cell in the sheet.
Now select the cells you want to protect and then Format > Cells > Protect.

Now go to Tools > Protect Document > Sheet and either supply a password or not as you wish.

Now only the cells you specifically selected are protected.

[kingfisher]

Rather than change all cells, change the default style. The menu selection is Format > Styles & formatting (F11) > 'default' : right-click and 'modify'.

[Colin Hunter]

Even simpler! Thanks Kingfisher.