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 - 04 Jul 2007 14:17 GMT
As ALWAYS, post your coding efforts for comments

Signature
Don Guillett Excel MVP
SalesAid Software
dguillett1@austin.rr.com
> 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
[quoted text clipped - 4 lines]
>
> Barney
Barney - 04 Jul 2007 14:44 GMT
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
> 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
[quoted text clipped - 4 lines]
>
> Barney
Don Guillett - 04 Jul 2007 15:07 GMT
>>I allowed sorting when I set up the macro. Do I need to 'unprotect' the
>>sheet YES
Where is it that you unprotected?

Signature
Don Guillett Excel MVP
SalesAid Software
dguillett1@austin.rr.com
> My macro:
>
[quoted text clipped - 34 lines]
>>
>> Barney
Dave Peterson - 04 Jul 2007 15:39 GMT
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.
> 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
[quoted text clipped - 4 lines]
>
> Barney

Signature
Dave Peterson