Class AllowEditRange (Excel VBA)

The class AllowEditRange represents the cells that can be edited on a protected worksheet.


To use a AllowEditRange class variable it first needs to be instantiated, for example

Dim aer as AllowEditRange
Set aer = ActiveSheet.Protection.AllowEditRanges(Index:=1)

The following procedures can be used to set variables of type AllowEditRange: AllowEditRanges.Add and AllowEditRanges.Item.

For Each

Here is an example of processing the AllowEditRange items in a collection.

Dim aer As AllowEditRange
For Each aer In ActiveSheet.Protection.AllowEditRanges
Next aer


Changes the password for a range that can be edited on a protected worksheet.

ChangePassword (Password)

Password: The new password.

Dim strPassword As String: strPassword = 
ActiveSheet.Protection.AllowEditRanges(1).ChangePassword Password:=strPassword


Deletes the object.



ActiveSheet.Protection.AllowEditRanges(1).Range =


Returns or sets the title of the range of cells that can edited on a protected sheet.

ActiveSheet.Protection.AllowEditRanges(1).Title =


Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

If you forget the password, you cannot unprotect the sheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

Unprotect (Password)

Password: A string that denotes the case-sensitive password to use to unprotect the range of cells. If the range isn't protected with a password, this argument is ignored.



Returns a UserAccessList object for the protected range on a worksheet.

Dim ualUsers As UserAccessList
Set ualUsers = ActiveSheet.Protection.AllowEditRanges(1).Users


Adds a range that can be edited on a protected worksheet. Returns an AllowEditRange object.

Add (Title, Range, Password)

Dim strTitle As String: strTitle = 
Dim aer As AllowEditRange
Set aer = ActiveSheet.Protection.AllowEditRanges.Add(Title:=strTitle, Range:=)


The following arguments are required:

Title (String) - The title of the range.

Range (Range) - Range object. The range allowed to be edited.

Optional arguments

The following argument is optional

Password - The password for the range


Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveSheet.Protection.AllowEditRanges.Count


Returns a single object from a collection.

Item (Index)

Index: The name or index number of the object.

Dim aerItem As AllowEditRange
Set aerItem = ActiveSheet.Protection.AllowEditRanges(Index:=1)