Protection of Cells with Data Validity

[drjcmartin]

Hi,

I've set up some cells in a spreadsheet to include data validation using the Data -> Validity menu option. Each cell presents a simple drop-down-list of values. I want the users of the spreadsheet to populate these cells only with the items in the drop-down-list. So far so good.

Now, if I don't protect the sheet (and cells) then it's possible for the user to delete the entire contents of these cells, removing the data validation in the process. They are then able to enter whatever they like. Clearly I don't want this because it defeats the whole point of the data validation that I've set up.

However, when I turn on sheet (and cell) protection it's no longer possible to select any of the values from the drop down list so the users can't populate the cells at all.

Naturally, I want the cell protection to protect my data validation but still allow users to populate the cells (with valid values). I can set this up easily in excel (which seems to have more options for protection) but I can't find a way to do this in Calc.

Please help!

Thanks,

Jon.

[Villeroy]

Locked cells and validation are contradictory to each other. Generally speaking, there is no security in spreadsheets. You can override validation at any time. Just change the criteria, delete cells or paste them over with other cells. "Protection" is just a flag in the document's xml (unzip and find "protect" in content.xml). Spreadsheets are designed to be "liberal". Every single cell can take any number, text or formula. There are no concepts of unique entities and data types like they are enforced in databases. It's more like a sketch board to calculate figures by relative positions (yes, with some extra tools for basic list-keeping and charting).
Everybody would like to use spreadsheets as databases, but spreadsheets do not provide for the necessary properties and concepts. This is not what this category of software had been designed for in the first place.

[drjcmartin]

Hi,

I don't think you've understood my post.

You say: "Locked cells and validation are contradictory to each other". Well, if the definition of a locked cell is one that cannot be modified at all then I would have to agree with you. But there are other notions of protection beyond the simple binary "locked"/"unlocked" and protecting data-validation criteria is one such notion. This concept allows a user to enter data which is valid (according to the data-validation criteria) but doesn't allow the user to change the actual criteria used to validate the data. This is such an obvious concept and I would argue that it just doesn't make sense to have data-validation without data-validation-protection. You say: "You can override validation at any time. Just change the criteria, delete cells or paste them over with other cells". Well, this is exactly the point I'm making because this is just what you don't want to happen if you have gone to the trouble of setting up data-validation criteria in the first place. I mean, what's the point of the data-validation if it can be overridden?

By the way, I'm not trying to use my spreadsheet as a database. I've worked alot with both spreadsheets and databases and understand the applications that each are suited to.

I'd like to emphasise that the functionality I'm looking for is available in Excel. I just want to know if it is also available in Calc. And if not, why not and will it be available in the future?

Thanks,

Jon.

[Villeroy]

No, I don't think that this will ever change. Another level of locking would break compatibility with the ODF standard and with other spreadsheet applications.

[fst]

Hi,

it's that easy, just place the Validity option on a cell, select Format-Cells-Protection and remove the protected flag.

Now use Tools-Protect Documents-Sheet and try it for yourself.

Frank

[Villeroy]

Hi,

it's that easy, just place the Validity option on a cell, select Format-Cells-Protection and remove the protected flag.

Now use Tools-Protect Documents-Sheet and try it for yourself.

Frank

Fine. Now you can edit the cell contents within the limits of the set up validity and you can change the validity itself as you can paste all kinds of content and formatting (which includes validation) over the validated cells. There is no second level of protection which allows validated editing of content alone while locking the validation itself. Excel has such a thing for macros: oSheet.Protect(userInterfaceOnly:=True)

[drjcmartin]

Thanks for your replies. It's a shame this isn't supported. Is there a way to get this feature added to the to-do list for calc?

Jon.

[Villeroy]

There is no need to stuff all those features into spreadsheets if there is an evolving database at hand. I think Excel-style spreadsheets are a far cry of the 80ies and 90ies of lonely desktop computing. No developer will spend too much energy in improving a dead horse. Better "compatibility" for the millions of existing documents is still a big issue. This gets a lot of attention as you can read in the release notes of OOo3. The whole ODF file format specification, as I understand it, deals with this main issue: "How can we transfer the work of billions of computer workers to something more future safe, something readable and processable?". Nobody wants a new "killer-app" based on more features.

[drjcmartin]

From your remarks it sounds like excel spreadsheets that use this feature will always be incompatible with Calc. Not to worry. Thanks for your time.

Jon.

[Villeroy]

From your remarks it sounds like excel spreadsheets that use this feature will always be incompatible with Calc. Not to worry. Thanks for your time.

Jon.

Huh? Can Excel lock validation separately from content? If Calc would support this, but Excel wouldn't, OOo had ignore the feature when saving in xls format. I believe it is the best if both applications do *not* support it for the sake of "compatibility" with as many spreadsheets as possible.

Edit: To answer my own question: No, Excel can not lock validation separately from content.

Excel's Data Validation feature is very useful, but it has a serious flaw: It is easy for a user to accidentally (or intentionally) delete the validation rules.

http://spreadsheetpage.com/index.php/ti ... t_deleted/

[drjcmartin]

Hi,

Yes, Excel CAN lock validation separately from content (I obviously didn't emphasise this point enough previously). I've attached an example spreadsheet to illustrate this. This spreadsheet was created in Excel (Microsoft Office Professional Edition 2003).

If you open this spreadsheet in Excel you can change the contents of the cell but the data-validation cannot be removed (which is exactly what I want). If you open it in Calc, nothing can be changed.

Jon.

[Hagar Delest]

Select the cell B7, and in its properties, go to the Cell Protection tab and uncheck the Protected option.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[drjcmartin]

Your reply suggests that you haven't really understood what the problem is. Have you read the preceding posts? Can you explain how your suggestion brings resolution to this issue?

Thanks,

Jon.

[Villeroy]

So I stand corrected and J. Walkenbach (the godfather of Excel sheets and VBA) does not keep his website up-to-date. The last Excel version I really used to know well was 9.0 (2000). I never used validation and protection for anything but child-proof protection against casual mistakes.

http://spreadsheetpage.com/index.php/ti ... tion_faq1/

How is worksheet protection different in Excel 2002 and later?

Excel 2002 and later provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002 or later, you are given a number of options that let you specify what the user can do when the worksheet is protected:
* Select locked cells
* Delete columns
* Select unlocked cells
* Delete rows
* Format cells
* Sort
* Format columns
* Use AutoFilter
* Format rows
* Use PivotTable reports
* Insert columns
* Edit objects
* Insert rows
* Edit scenarios
* Insert hyperlinks

Does validation belong to "Format cells"?

If I protect my worksheet with a password, is it really secure?

No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.
So are you saying that protecting a worksheet is pointless?

Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.
Are there any other reasons to protect a worksheet?

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

OK, I protected my worksheet with a password. Now I can't remember the password I used.

First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.

Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.

If that fails, you can try one of the commercial password-breaking programs. I haven't tried any of them, so I have no recommendations.

In case of lost passwords, OpenOffice.org is a non-commercial program which simply ignores all the non-encrypting passwords you may have set for document loading, document protection (collection of sheets), sheet protection, and macro protection.

[Hagar Delest]

Your reply suggests that you haven't really understood what the problem is. Have you read the preceding posts? Can you explain how your suggestion brings resolution to this issue?

When I do so:
- I can change B7 according to your validity list.
- I cannot put another entry in B7 if not in the validity list.
- The rest of the sheet is protected.

Have I missed something?

BTW, you're indeed right, I missed the fst's post which gives the very same reply. My apologizes to fst! And with this solution, the situation is exactly the same as with Excel (I tried with your file).

[Villeroy]

Hagar, as a nasty evil-doer you can override the validation in Calc. Excel can be tweaked so it allows validated changes to the content without allowing to change the validation rule or paste over unvalidated cells.

[drjcmartin]

Was just about to reply to Hagar but you beat me to it Villeroy.

I'd just like to add that you don't need to be a nasty evil-doer. I'm not expecting to be able to make my spreadsheet bullet-proof - I just want to be able to protect the data-validation from being destoyed accidentally by the users who are filling it in. The trouble at the moment is that all it takes is a simple cut and paste operation to do that which is precisely the sort of operation I would expect my users to use whilst filling out the sheet.

Jon.

[Hagar Delest]

I see your point (even if I don't know how, except if you use the fact that the sheet password is cannot be imported in .xls file format with Calc - but I'm not a Calc power user!) but if the OP need is just avoid mistakes, it may be enough.

Edit: OK, I see about the copy/paste to override the cell. But again, same behavior with Excel.

[Villeroy]

Yes, this is a very useful feature they introduced in Excel, indeed. (which version, by the way?) Since the ODF specification is independent from the implementation an office application could solve this in this way:
If a cell is effectively locked and validated at the same time, allow for validated input. No need to change the ODF specification or add any feature. Well, this could undermine some of the existing files where validation has been overridden by protection.

Off topic: My favourite spreadsheet Gnumeric handles this situation completely insane. You can paste into locked cells. They remain locked while swallowing all the content and properties of the pasted cells. If a locked cell is validated by drop-down you can use the drop-down list without beeing able to type.

[drjcmartin]

Edit: OK, I see about the copy/paste to override the cell. But again, same behavior with Excel.

Excel actually behaves quite differently. The key thing with the example spreadsheet I sent is that, in Excel, you can't remove the validation from the cell B7. You can't cut it and you can't paste over it. You CAN paste into the cell and in doing so you can even override the data-validation criteria (Microsoft state explicitly that the data-validation only works when typing into the cell and not pasting into it - a deficiency in my opinion but no doubt there is a good reason for it (?)) BUT the data-validation criteria are not actually destroyed by the paste operation. This would arguably be of limited value if it were not for the fact that you can highlight all cells that have invalid data with respect to their validation criteria (Tools -> Formula Auditing -> Show Formula Auditing Toolbar : Cirle Invalid Data). If the data-validation criteria were removed in a paste operation the "Circle Invalid Data" would have no criteria to validate against.

The difference with Calc is that (with the cell unlocked) if you cut the cell or paste into it all of the data-validation criteria are destroyed along with the contents of the cell.

Personally, I think the Excel implementation has a serious flaw in allowing invalid data to be pasted into cells but, by preserving the data-validation criteria themselves, it does at least provide a better level of protection than is currently available in Calc.

Jon.

[alexstep]

Should there be some workaround in OO Calc through Macros ?

[miasma.gajs]

I too would like cell validation rules to be protected, as requested and exhaustively described by OP. As there has been no response to this thread I assume that this has not been implemented - has it even been accepted as an enhancement request?

[RusselB]

Please feel free to submit bugs, feature requests, patches or comments.

[Villeroy]

As always, the solution could be a database.

[Robert.Conklin]

drjcmartin, in your excel spreadsheet above, how exactly did you prevent a user from pasting anything other than an option in the drop down list into cell B7? I have gotten to this point and am stuck. Thanks