Unable to sort on protected sheet

[honkin]

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

 

[Saba Sabaratnam]

I think that you could have to turn on Filter button before you protect your worksheet.

Kind regards

Saba

 

[honkin]

ah, I hadn't thought of that. Will unprotect it now and try.

Thanks so much for the reply

 

[honkin]

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

 

[Saba Sabaratnam]

No problem

 

[honkin]

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.

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]

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]

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

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]

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.

 

[antman2988]

Are there multiple sheets or just one?