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

[asklucas]

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.

 

[Dave Peterson]

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.

 

[asklucas]

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]

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

 

[asklucas]

Dear Dave,

thank you for your answer.

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

Lucas.