Sort a 'Protected' sheet

[Barney]

Using Excel 2002, when I try to sort a protected sheet with a macro, I get a
dialog box with 'Run time error 1004'. I allowed sorting when I set up the
macro. Do I need to 'unprotect' the sheet with the macro and then 'protect'
it again after the macro does its sorting? Any suggestions?

Thanks,

Barney

 

[Don Guillett]

As ALWAYS, post your coding efforts for comments

 

[Barney]

My macro:

Sub SortScoresDay4()
'
' SortScoresDay4 Macro
' Macro recorded 7/3/2007
'

'
Range("C4:N69").Select
Selection.Sort Key1:=Range("K4"), Order1:=xlAscending, Key2:=Range("L4")
_
, Order2:=xlAscending, Key3:=Range("M4"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
Selection.Sort Key1:=Range("N4"), Order1:=xlAscending, Key2:=Range("J4")
_
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=
_
xlSortNormal
Range("C4").Select
End Sub

 

[Don Guillett]

I allowed sorting when I set up the macro. Do I need to 'unprotect' the
Where is it that you unprotected?

 

[Dave Peterson]

Allowing the users to sort via that Tools|Protection|protect sheet dialog (or
the equivalent in code) only gives permission to the users to sort the range.

If you want to sort the range in a macro, you can either unprotect the sheet, do
the sort, and reprotect the sheet--or you could protect the sheet in code and
add UserInterfaceOnly:=true to your code.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
'add all the other settings that you want to the following line
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

It needs to be reset each time you open the workbook. That's why I used the
Auto_Open routine.