filtering and sorting in protected sheets

[Christian Galbavy]

Hy!

I have the following problem:
I have a sheet with some locked cells, the sheet is protected, but I have
selected, that filtering and sorting (and also cell manipulation) should be
allowed. But the AutoFilter can not be activated, and when I try to sort the
cells, I get the message that the cells are locked and sorting is not
possible.
I do not understand this problem.

Thanks for any information.
Regards
Christian

 

[Dave Peterson]

xl2002 added some options to allow filtering and sorting of a protected
worksheet.

Until then, I think you'd have to give the user a way to sort the data--maybe a
macro that would unprotect the sheet, sort the data and then reprotect the
sheet.

But for the autofilter, you can protect the worksheet in code (if the autofilter
is already applied):

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Mladen_Dj]

cells, I get the message that the cells are locked and sorting is not
possible.

You can't perform sort on locked cells when sheet is protected. You must
unlock all cells in the range. If you don't want user can change cells then
uncheck "Select unprotected cells" in Protect sheet dialog box. User will
still be able to filter and sort table, but could not be able to
select/change cells. I play with this options, and find one weaknes of this
method. User is able to navigate in worksheet by using tab button, and
because cells are unlocked can delete content of cells but is unable to
write to cells. Its really strange to me???