Unable to sort on protected sheet

honkin

Active Member
Joined
Mar 20, 2012
Messages
287
Office Version
  1. 2016
Platform
  1. MacOS
I have a template I use to create new results sheets. The first 16 rows are locked and have boxes with Profit & Loss and other metrics and the data headers are in row 17 with the data all starting from row 18.

As I said, the first 16 rows are locked and when I protect the sheet, I select the things to allow, like Sort, Filter & Select Unlocked Cells, yet when I click on the Data menu, Filter is greyed out.

Any ideas why this is the case and how is it possible to have the sheet perform as I wish?

cheers
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
I think that you could have to turn on Filter button before you protect your worksheet.

Kind regards

Saba
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
287
Office Version
  1. 2016
Platform
  1. MacOS
ah, I hadn't thought of that. Will unprotect it now and try.

Thanks so much for the reply
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
287
Office Version
  1. 2016
Platform
  1. MacOS
I think that you could have to turn on Filter button before you protect your worksheet.

Kind regards

Saba
Yes indeed Saba. Not something I would have considered, it it works. I will now have to open the actual template and amend it so it works for all sheets created using it

Thanks again
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
287
Office Version
  1. 2016
Platform
  1. MacOS
No problem :)
Hello again Saba

I just have a quick question re this issue.

The resolution certainly works to allow filtering on a protected sheet which has locked cells, but the downside of that is it is impossible to turn the autofilter off. The only way to do it is to individually clear each filter used, which seems a cumbersome way to go, especially if there are a dozen or so filters.

Any thoughts on if it is possible to have the autofilter function as per normal on a protected sheet which has filter enabled in the protection criteria?

I have seen in another post a suggestion which uses vba code to protect a sheet yet allow autofiltering.

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim xSheet As Worksheet
    Dim xPsw As String
    xPsw = "XXXXXXXXXX"
    For Each xSheet In Worksheets
        xSheet.Protect xPsw, AllowFiltering:=True
    Next

I am just wondering if this protects ONLY the cells which are locked or all cells. I have selected locked on the cells I do not wish to be changed, so am hoping to be able to still have access to the unlocked cells, but be able to filter

cheers
 

antman2988

Board Regular
Joined
Jun 28, 2018
Messages
78

ADVERTISEMENT

Hi honkin, I wouldn't go the VBA route unless you have to. Are you enabling the "Use AutoFilter" parameter when protecting the sheets?
 

honkin

Active Member
Joined
Mar 20, 2012
Messages
287
Office Version
  1. 2016
Platform
  1. MacOS
Hi honkin, I wouldn't go the VBA route unless you have to. Are you enabling the "Use AutoFilter" parameter when protecting the sheets?
cheers and thanks for your reply antman2988

Yes, unlocked cells and filter are selected in protection

Screen Shot 2564-07-30 at 07.25.33.png


As I indicated to Saba, if I don't turn the autofilter on before protecting the sheet, it is not possible to turn it on, but if I do turn it on prior to protecting the sheet, there is no way to turn it off other than clearing each filter individually

Any thoughts at all? The first 17 rows of the sheet have some data boxes which autocalculate as the sheet is filtered, they are all locked so cannot be altered, just the formulas operate, so it's really something I need to be able to get right

Thanks again
 

antman2988

Board Regular
Joined
Jun 28, 2018
Messages
78
Ah, OK. I misread that the first time. It's not possible to clear filters when a sheet is protected. You'll have to unprotect the sheet, clear the filters, and then protect the sheet or run a macro to accomplish the same task.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,580
Messages
5,770,976
Members
425,654
Latest member
Kotika

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