AllowEditRange and AllowEditRanges Members

Use the Protection object’s AllowEditRanges property to get a reference to the AllowEditRanges collection. The AllowEditRanges collection and AllowEditRange object provide the following members. Key members (shown in bold) are covered in the following reference section:

1 Collection only

Add 1

ChangePassword

Count 1

Delete

Range

Title

Unprotect

Users

Use the AllowEditRanges collection to create ranges that allow edits by specific users. Excel prevents changes to ranges of cells that are protected and locked. The AllowEditRanges settings automatically unlock ranges of cells for the users included in the user-access list.

You must remove protection from a worksheet before you can add user-level permissions. For example, the following code unprotects a worksheet, creates a range that allows user-level permissions, and then restores protection:

    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange, _
      usr As UserAccess
    Set ws = ThisWorkbook.Sheets("Protection")
    ws.Unprotect "Excel2003"
    Set aer = ws.Protection.AllowEditRanges.Add("Edit Range", ws.[a1:c4])
    Set usr = aer.Users.Add("Power Users", True)
    ws.Protect "Excel2003"

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial