Can a sheet with locked data be sorted without setting 'Allow users to edit ranges'?

Hello,

I have a worksheet of data that has a header row and the filter button is applied to the header row allowing to filter and sort data by column.

Some rows of data are locked while others are not or partial rows of data are 'locked'. The worksheet is protected.

With these settings, the filters work but you are not able to sort because of the locked cells.

I have found that if you set 'Allow users to edit ranges' located under the 'Review' tab to include the entire sheet of data, you are then able to sort the data even if the sheet is locked. But then you run into other problems, namely if you allow users to select locked cells, they can alter/delete data since an editable range has been set.

So my question is, can a sheet with locked data be sorted without setting an editable range via the 'Allow users to edit ranges' under the 'Review' tab?

Thanks for any help.

TV

Holger

I had thought of that approach a while back but I was not able to find a way to detect via VBA the action of sort/filter via the filter button. As for UserInterfaceOnly, I tried that one out for other actions in my workbook and it proved to be very unreliable so I resorted to unprotect, make change, protect for all actions to change locked cells.

TV

Hi Holger, I can certainly take that approach and that may be my only choice if there is no other way to avoid using 'Allow users to edit ranges', making locked data susceptible to changes. I just will have to think of a way to allow users to sort on more than one column though, perhaps an iterative process asking for column order.

Cheers,

TV

Holger, thanks for the heads up on the Advanced Filter. I never knew about it until now, however my data is all on one sheet so it is not a good fit.

Cheers,

TV