How can I sort when the worksheet is locked?

[Neon520]

Hi everyone,

Is there a way to enable the sorting feature in Excel when the worksheet is
locked?

Thanks,
GU

 

[ryguy7272]

Locked means protected, right. Most of the controls are disable when this
feature is utilized. If you don't have the password, the best you can hope
for is to copy/paste to a new sheet and work from there.

Regards,
Ryan--

 

[Neon520]

Yes, by locked I mean protected.
My goal is to create a template that consist of formula and I need to
protected the sheet to prevent lost of formula and I need to have the sorting
feature available once the data has been input to the template.

 

[Gord Dibben]

Most common method is to use code to unprotect, do the sort, then reprotect.

Assuming version of Excel is later than 2000. If 2000 or earlier read the
quoted bit below from Jerry Latham and use the second set of code.

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub

Ok, that explains it - you're probably running Excel 2000? The "Data
Option1" (there's also a 2 and 3) didn't exist in 2000. The macro was
probably created on machine with Excel 2003 on it. Easy to fix, just delete
everything in that line from the comma just ahead of DataOption1 to the end
of the line. Code should end up looking like:

Sub sortit()
Dim coltosort As Range
Set coltosort = ActiveSheet.Range("A6:A60")
ActiveSheet.Unprotect Password:="justme"
coltosort.Select
Selection.Sort Key1:=coltosort.Cells(2), _
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
With ActiveSheet
.Protect Password:="justme"
.EnableSelection = xlNoRestrictions
End With
End Sub


Gord Dibben MS Excel MVP