AllowEditRanges Collection

Protection AllowEditRanges AllowEditRange

A collection of all the AllowEditRanges objects that represent the cells that can be edited on a protected worksheet.

Using the AllowEditRanges Collection

Use the AllowEditRanges property of the Protection object to return an AllowEditRanges collection.

Once an AllowEditRanges collection has been returned, you can use the Add method to add a range that can be edited on a protected worksheet.

In this example, Microsoft Excel allows edits to range "A1:A4" on the active worksheet and notifies the user of the title and address of the specified range.

Sub UseAllowEditRanges()

    Dim wksOne As Worksheet

    Set wksOne = Application.ActiveSheet

    ' Unprotect worksheet.
    wksOne.Unprotect

    ' Establish a range that can allow edits
    ' on the protected worksheet.
    wksOne.Protection.AllowEditRanges.Add _
        Title:="Classified", _
        Range:=Range("A1:A4"), _
        Password:="secret"

    ' Notify the user
    ' the title and address of the range.
    With wksOne.Protection.AllowEditRanges.Item(1)
        MsgBox "Title of range: " & .Title
        MsgBox "Address of range: " & .Range.Address
    End With

End Sub