List Protection Problem

polisasimo

New Member
Joined
Jul 27, 2007
Messages
12
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jul 27, 2007
Messages
12
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

New Member
Joined
Jul 27, 2007
Messages
12
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Perhaps allowing the range to be edited, or use of VBA. Which would you be most comfortable with?

Dave
 

qc02547

New Member
Joined
Jul 19, 2007
Messages
3
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,181,365
Messages
5,929,548
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top