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." <(E-Mail Removed)> wrote in message
news:85425FA2-297F-43D8-B960-(E-Mail Removed)...
> 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