View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
excelus excelus is offline
external usenet poster
 
Posts: 2
Default 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