WSheet Protection Flexibility [Allow User to Use Autofilter]

[kpark91]

Hello,

As the title suggests, I want the users to be able to use autofilter a database I've created.

It is a worksheet with only a huge table which takes space from A1 to AL654 and increasing (since it was a table, it was extremely easy to add and delete datas with formulas).
Since I created the table in XL2007, it is aesthetically appealing (with colours ) and added filters by itself.

Anyways, the point is I am extremely happy with this table and it would be great if I don't have to change the table.

Now, the problem arises when I am protecting the sheets.

I've locked all my cells and protected the whole worksheet with the options of allowing the user to sort and autofilter, and other default options.

Then when I try to sort it after saving it and reopening the worksheet.
I get a message that I should unprotect the worksheet.

This worksheet is for general people's use so I cannot let other people change values in the cells.

Could you help me please?

Thank you :)

[HansV]

AutoFilter will work with locked cells on a protected sheet, if you ticked the corresponding check box in the Protect Sheet dialog.
But sorting moves cells around; you can't do that with locked cells on a protected sheet, only with unlocked cells.
If you really need users to be able to sort locked cells, you have to provide your own sort buttons; the code behind the buttons should unprotect the sheet, sort the data, then protect the sheet again.

[kpark91]

Sigh..
Thanks for the answer, HansV.
Without this info, I would have been trying to protect countless times and waste time.

I would love to use a macro to do it
but macros should not be used for general population.

Thanks again

[kpark91]

Is there anyway I can protect the cell values from users without a macro?
and without locking the cells?

[HansV]

Is there anyway I can protect the cell values from users without a macro?
and without locking the cells?

No, not really.

You could set custom data validation on unlocked cellls with the formula =FALSE. This would prevent users from editing cell values, but they would still be able to clear cells, and copy and paste cells. More sophisticated protection requires VBA.

[kpark91]

Ok.

Thank you, HansV.