List Protection Problem

[polisasimo]

Hello Everyone,

I am new to the forum and this is my first post. After searching the forum for a problem similar to my own I, with limited sucess, I have decided to pose the following scenario.

I have a workbook with several pages each containing a group of data that has been transformed into a list. This allows any one viewing this workbook the option to filter some of the data based on the list functionality.

I would like to apply some protection to the data that is contained in the list but when I apply security to the worksheet with the header columns unlocked, thus making them selectable the sorting and filtering of the data is not available.

I assume this is because the cells which hold the lists data are locked and the sheet is protected.

Any ideas on a way to allow list functionality (sorting, filtering) while still protecting the contents of the list?

Thanks in advance.

 

[dave3009]

Hi

Welcome to the Board

I believe you can choose an option to allow filtering and sorting when protecting.

Have a look at your options whe you choose Tools>>Protection>>Protect Sheet

HTH


Dave

 

[polisasimo]

Thanks for the info Dave.

There is a selectable sort checkbox on the protection options but this does not resolve list functionality. What happens is the control bar for the list column header simply goes unavailable when the lists contens are protected, regardless of the status of the sort checkbox.

 

[Datsmart]

Depends on what version of Excel you're using:
With Excel 2002 or later, when you protect the sheet, you have the option to allow users to use autofilter (one of the checkboxes).
In 2000 or earlier, try Andrew's suggestion here:
http://216.92.17.166/board2/viewtopic.php?t=53916

In either case, you must setup the AutoFilter prior to protecting the sheet, plus, no sorting is allowed at all, just autofiltering.

 

[polisasimo]

Furthermore,

It appears that the locking/unlocking the the content cells is irrelavent. I think adding protectin to a worksheet simply disables all list functionality. Neway around this?

 

[dave3009]

Perhaps allowing the range to be edited, or use of VBA. Which would you be most comfortable with?

Dave

 

[qc02547]

I'm having the same trouble with Excel 2003. I have data that I'm using Autofilter on, with one column's cells that are locked, containing a formula to make a calculation.
Even though I check the "Select locked cells", Select unlocked cells", "Use Autofilter", and "Sort" boxes, it still won't let me sort data.
Personally I think it's a bug. What else would those check boxes be for?
Good luck!

 

[Datsmart]

As I mentioned in my post on Friday, "no sorting is allowed at all, just autofiltering."
Even though there is a SORT checkbox when setting it up, sorting would move data, and that is not allowed under protection.