I am trying to set a range to allow users to sort a range. I thought it was
working, but I get a Run-time error '1004': Application-defined or
object-defined error" when it gets to the AllowEditRanges object.

Here is the code (any suggestions to make it more efficient would also be
appreciated):
Sub ProtectAll()
Dim wks As Worksheet
Dim strPassword As String

strPassword = InputBox("Enter the password", "Password")

For Each wks In Worksheets
wks.Protection.AllowEditRanges.Add _
Title:="SortRange", _
Range:=Range("A29:S" & Range("B65000").End(xlUp).Offset(-23,
0).Row)
Next wks
For Each wks In Worksheets
wks.EnableSelection = xlNoRestrictions
wks.Protect Password:=strPassword, _
Contents:=True, _
AllowFormattingCells:=True, _
AllowSorting:=True
Next wks
End Sub

Josh,

It appears that your Range call has some type of circular reference. If you
can provide some instructions on what you are trying to attempt, maybe
someone could help out a bit more (i.e. - what is the specified range you
are wanting to work with, etc.)


Mark Ivey


"Josh O." wrote in message
...
I am trying to set a range to allow users to sort a range. I thought it
was
working, but I get a Run-time error '1004': Application-defined or
object-defined error" when it gets to the AllowEditRanges object.

Here is the code (any suggestions to make it more efficient would also be
appreciated):
Sub ProtectAll()
Dim wks As Worksheet
Dim strPassword As String

strPassword = InputBox("Enter the password", "Password")

For Each wks In Worksheets
wks.Protection.AllowEditRanges.Add _
Title:="SortRange", _
Range:=Range("A29:S" & Range("B65000").End(xlUp).Offset(-23,
0).Row)
Next wks
For Each wks In Worksheets
wks.EnableSelection = xlNoRestrictions
wks.Protect Password:=strPassword, _
Contents:=True, _
AllowFormattingCells:=True, _
AllowSorting:=True
Next wks
End Sub