deleting an AllowEditRange
Try:
activesheet.protection.alloweditranges(1).delete
1 - the first range, it can be the name of the range.
"Eric" wrote:
I'm getting an "Application or Object defined error" when I run the
following and it tries to delete an AllowEditRange
Private Sub defineEditableRanges()
' defines the areas that can be edited on the protected worksheet.
Const roomsTitle = "RoomNamesAndNumbers"
Const deptTitle = "DeptAndPersonnel"
Dim myRange As Range
Dim wkSheet As Worksheet
Set myRange = Columns("A:B")
Set wkSheet = ActiveSheet
' first clear out any old ones ----------------------------- I need
something like this or else I'll get an error when I try to add the same
range again
For Each er In wkSheet.Protection.AllowEditRanges
If er.Title = roomsTitle Then
er.Delete ------------------------------------------ Where I
get the error
ElseIf er.Title = deptTitle Then
er.Delete
End If
Next
wkSheet.Protection.AllowEditRanges.Add Title:=roomsTitle, Range:=myRange
Set myRange = Rows("4:5")
wkSheet.Protection.AllowEditRanges.Add Title:=deptTitle, Range:=myRange
End Sub
TIA for any insights
|