Sort-function of AutoFilter doesn't work, when sheet is protected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

asklucas - 24 Aug 2006 09:55 GMT

Hi there,

I have a sheet with some columns of locked cells and an AutoFilter in
the header-line of the sheet. I have to protect the worksheet to
protect the formulas in some cells.

When protecting, I choose to allow all users to use AutoFilter and Sort
function.

However, when I open the sheet in protected mode, I can use the
AutoFilter-function to filter the data, but then cannot sort the data.
A warning appears, that I have to unprotect the sheet in order to sort.

Has anyone got an idea how to solve that?

I'm using Excel 2003 SP2.

Lucas.

Reply to this Message

Dave Peterson - 24 Aug 2006 13:45 GMT

Make sure that all the cells in the range to be sorted are unlocked
(format|Cells|protection tab).

And make sure that there are no locked cells with data in them that are adjacent
to the range to be sorted.

> Hi there,
>
[quoted text clipped - 14 lines]
>
> Lucas.

Signature

Dave Peterson

Reply to this Message

asklucas - 28 Aug 2006 01:57 GMT

Dear Dave,

thank you for your soon answer!

What I do not get is: Why can I select "allow sorting" when protecting
a sheet, when it is in the end generally not possible to sort a
protected sheet. If I unlock all the cells within the sorting area,
which formulas I wanted to protect, the whole protection function is
useless. And, since it's possible to delete and insert lines in my
document when it is protected, there is no reason not to allow sorting.

Do you have any solution without unlocking my sheet?

Lucas.

Dave Peterson schrieb:

> Make sure that all the cells in the range to be sorted are unlocked
> (format|Cells|protection tab).
[quoted text clipped - 20 lines]
> >
> > Lucas.

Reply to this Message

Dave Peterson - 28 Aug 2006 03:44 GMT

Maybe you could provide a macro that would unprotect the worksheet, sort the
data, and reprotect the worksheet.

> Dear Dave,
>
[quoted text clipped - 41 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

asklucas - 29 Aug 2006 08:54 GMT

Dear Dave,

thank you for your answer.

I found some sorting macros in other postings, I will modify them.

Lucas.

Dave Peterson schrieb:

> Maybe you could provide a macro that would unprotect the worksheet, sort the
> data, and reprotect the worksheet.
[quoted text clipped - 44 lines]
> > >
> > > Dave Peterson

Reply to this Message