Programming Excel with VBA and .NET

UserAccess and UserAccessList Members

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

1 Collection only
Add 1	AllowEdit
Count 1	Delete
DeleteAll 1	Name

Use the UserAccessList collection to add users to the user-access list of an edit range on a protected worksheet. You can add individual users or groups to the user-access list, but the names must be valid user or group names for your system. For example, the following code adds the built-in Users group to the access list for an edit range:

    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange, _
      usr As UserAccess
    Set ws = ThisWorkbook.Sheets("Protection")
    Set aer = ws.Protection.AllowEditRanges("Edit Range")
    Set ual = aer.Users
    Set usr = ual.Add("Users", True)

The UserAccessList collection does not support the For Each construct in Visual Basic. Instead, you must use a For statement with a counter to get each item in the collection as shown here:

    For i = 1 To ual.Count
        Set usr = ual(i)
        Debug.Print usr.Name
    Next