VBA/Excel/Access/Word/Access/User Access

Add Edit Range

 
Sub AddEditRange()
    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], "pass1")
    Set usr = aer.Users.Add("Power Users", True)
    ws.Protect "Excel2003"
End Sub

Build user list

 
Sub BuildUserList()
    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("Edit Range")
    Set ual = aer.Users
    Set usr = ual.Add("Administrators", True)
    Set usr = ual.Add("Users", True)
    Set usr = ual.Add("Guests", False)
    For i = 1 To ual.Count
        Set usr = ual(i)
        Debug.Print usr.Name
    Next
    ws.Protect "Excel2003"
End Sub

Delete user list

 
Sub DeleteUserList()
    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("Edit Range")
    Set ual = aer.Users
    ual.DeleteAll
    ws.Protect "Excel2003"
End Sub

Remove Edit Ranges

 
Sub RemoveEditRanges()
    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange
    Set ws = ThisWorkbook.Sheets("Protection")
    ws.Unprotect "Excel2003"
    For Each aer In ws.Protection.AllowEditRanges
        aer.Delete
    Next
    ws.Protect "Excel2003"
End Sub

Show Edit Ranges

 
Sub ShowEditRanges()
    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange
    Set ws = ThisWorkbook.Sheets("Protection")
    For Each aer In ws.Protection.AllowEditRanges
        Debug.Print aer.Title, aer.Range.Address
    Next
End Sub

Unlock Edit Range

 
Sub UnlockEditRange()
    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange
    Set ws = ThisWorkbook.Sheets("Protection")
    For Each aer In ws.Protection.AllowEditRanges
        aer.Unprotect
    Next
End Sub