Sort a protected worksheet

[srdavisgb]

I have created a protected worksheet and saved the workbook with vba. Users must be able to sort the worksheet but, I don't want them to be able to change content in the locked cells (autofilter works). I have recorded the vba to "Allow Edit Ranges" and added to my code. It appears I have left something out...

I would appreciate any suggestions on how to correct the code. The code below is what I recorded that does not work:

ActiveSheet.Protection.AllowEditRanges.Add Title:="Autosort", Range:=Range( _"A1:U8000")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True

This code will produce the desired workbook with protection on the worksheet but, the user can not sort. How do I modify this code to allow sorting?

ActiveSheet.Protect Password:="Winter2018", DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlNoRestrictions


Thank you.

 

[MARK858]

Please see the below from the AllowSorting section of the Worksheet.Protect Method from MSDN...

True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False .

P.S. in future can you please use Code Tags rather than colour your code red. To apply code tags paste the code in the thread, select the code and click the # icon.


Link to Worksheet.Protect Method

 

[MARK858]

Although the first code you posted with AllowEditRanges should work. What is it stating when you try the sort?

 

[srdavisgb]

Although the first code you posted with AllowEditRanges should work. What is it stating when you try the sort?

Thanks for your response. Actually, the sort does work. But, the cells that were locked can now be changed by the user. What I'm trying to do is create a worksheet that has all but three columns protected. The user can update only in these three columns. The code does password protect the worksheet and when I review the protection format the cells are still locked.

In regards to the statement, "True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False", what would be the purpose of protecting the worksheet if you are not able to lock the cells? I was hoping the "AllowEditRanges" code would unlock the protected cells, allow the sort to occur and re-protect the worksheet and lock the protected cells.

In any event, is there a way to sort a protected worksheet that has locked cells?

 

[MARK858]

Are you manually sorting the cells or via code? and if manually sorting is putting a button on the sheet and sorting by code an option? c

As for what is the purpose of protecting a sheet with the cells unlocked, the purpose is you only unlock the cells that you want affected you don't need to have every cell unlocked.