Sort Protected Sheet

[Erika]

I have a huge spreadsheet that contains a list. I don't want people to be
able to modify the data in the spreadsheet but I do want them to have the
ability to sort.

I have protected the spreadsheet and checked sort however they get an error
when they try to sort. Am I missing a step?

 

[DStrong]

Hi Erika-
First are you password protecting the sheet or just turning on protection.
If the later, I have some code that will do the sort for the user. Let me
know and I will see what I can do to assist you.

David

 

[Gord Dibben]

Sorting on a protected sheet is only possible within an unlocked range on
that sheet.

Best is to use a macro to Unprotect, sort then reprotect.

Placed in a general module..........................

Sub sortit()
ActiveSheet.Unprotect Password:="justme"

your sort code goes here.

ActiveSheet.Protect Password:="justme"
End Sub

If by "list" you mean a Data>List you can use alternate code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objlist As ListObject
Set objlist = Me.ListObjects(1) 'adjust the (1) if needed
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, objlist.Range) Is Nothing Then
Me.Unprotect Password:="justme"
Else
With Me
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right-click the sheet tab and "View Code"

Copy/paste the code into that module. Edit to suit then Alt + q to return
to the Excel window.


Gord Dibben MS Excel MVP