automatic column sorting problem when protecting sheet

[Guest]

I used the following formula to autosort data in a column with good results:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The problem is that when I protect the sheet, I get the following error:

runtime error '1004':
Sort message of Range class failed

whereupon it gives me the option to debug. The cells in this particular
column are not protected.

How do I fix this? Thanks in advance for your help.

 

[Guest]

Two solutions, one of which should work:

1) When you protect the sheet, there is a list of things you can allow
the user to do, with checkboxes. Try ticking the "Sort" check box. This
might work in itself.

2) If the above does not work, then add the following lines to your code:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect (password)

Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1,MatchCase:=False,
Orientation:=xlTopToBottom

Activesheet.Protect (password)

 

[Guest]

The first solution worked like a charm. Thanks again.

 

[Guest]

You're welcome and thank you for letting me know.