Is autofilter & sort disabled in protected cells?


M

Munchkin

My spreadsheet is protected, but I have checked off the boxes to allow users
to use autofilter & sort, but since my cells are protected (because I don't
want anybody to change any records in the document) the autofilter sort
feature does not work. Is there any way around this? Thanks.
 
Ad

Advertisements

T

Tom Hutchins

If you apply AutoFilter to the sheet before protecting it (and check the 'use
autofilter' option), AutoFilter will work on the protected sheet. Excel will
never sort locked cells, even if you checked the 'sort' option when you
protected the sheet. The two alternatives usually given are:
1) unlock all the cells to be affected by the autofilter or sort (which
defeats the purpose of protecting the sheet); or
2) create a macro (maybe run from a button on the sheet) which will
unprotect the sheet, do the sort, and re-protect the sheet.

You might consider making the file read-only instead of using sheet
protection. In Windows Explorer, right-click on the (closed) workbook and
select Properties. Click a check in the read only checkbox, then click OK.
Your users can do everything with the file in Excel except to save it (they
could Save As another name or in a different folder). Or, maybe some
combination of sheet protection + read-only will work best for you.

By requiring confirmation, read-only status also prevents the file from
being accidentally deleted, renamed, or copied over.

Hope this helps,

Hutch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top