The AllowEditRange object allows a specified range of cells on a worksheet to be password protected from editing. Once a range has been protected in this way, and the entire worksheet has been protected, any attempt at editing cells in that range will require the password.
Here is some code that assigns a password to a range on the active worksheet. It also demonstrates the use of the AllowEditRanges collection.
Dim ws As Worksheet Dim i As Integer
Set ws = Application.ActiveSheet
' Remove protection ws.Unprotect
' Delete all current protection ranges
For i = 1 To ws.Protection.AllowEditRanges.Count
Debug.Print ws.Protection.AllowEditRanges(i).Title ws.Protection.AllowEditRanges(i).Delete Next
' Add a protection range ws.Protection.AllowEditRanges.Add Title:="Headings", Range:=Range("A1:A4"), Password:="hide"
' Protect sheet (else protection range is not enabled) ws.Protect End Sub
The properties of the AllowEditRange object are: Range
Returns or sets the range associated with the AllowEditRange object.
Returns or sets the title (i.e. name) of the range associated with the AllowEditRange object.
Returns the collection of UserAccessObjects associated with the AllowEditRange object. For more on this, see the section on the UserAccess object.
The methods of the AllowEditRange object are: ChangePassword
Changes the password associated with the AllowEditRange object.
Deletes the AllowEditRange object. Unprotect
Unprotects the workbook.
Was this article helpful?