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


Count 1


DeleteAll 1


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

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

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.