Class AllowEditRange (Excel VBA)

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

Set

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

Dim strTitle As String: strTitle = 
Dim rngRange As Range: Set rngRange = 
Dim prt As Protection: Set prt = 
Dim aer As AllowEditRange
Set aer = prt.AllowEditRanges.Add(Title:=strTitle, Range:=rngRange, Password:="")

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

For Each

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

Dim prt As Protection: Set prt = 
Dim aerAllowEditRange As AllowEditRange
For Each aerAllowEditRange In prt.AllowEditRanges
	
Next aerAllowEditRange

ChangePassword

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

Syntax : expression.ChangePassword (Password)

Password: The new password.

Sub UseChangePassword() 
 
 Dim wksOne As Worksheet 
 Dim strPassword As String 
 
 Set wksOne = Application.ActiveSheet 
 
 ' Establish a range that can allow edits 
 ' on the protected worksheet. 
 
 strPassword = InputBox("Please enter the password for the range") 
 wksOne.Protection.AllowEditRanges.Add _ 
 Title:="Classified", _ 
 Range:=Range("A1:A4"), _ 
 Password:=strPassword 
 
 strPassword = InputBox("Please enter the new password for the range") 
 
 ' Change the password. 
 wksOne.Protection.AllowEditRanges("Classified").ChangePassword _ 
 Password:="strPassword" 
 
 MsgBox "The password for these cells has been changed." 
 
End Sub

Delete

Deletes the object.

Title

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

Unprotect

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.

Syntax : expression.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.

Users

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

Sub DisplayUserName() 
 
 Dim wksSheet As Worksheet 
 
 Set wksSheet = Application.ActiveSheet 
 
 ' Display name of user with access to protected range. 
 MsgBox wksSheet.Protection.AllowEditRanges(1).Users(1).Name 
 
End Sub

AllowEditRanges.Add

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

Syntax : expression.Add (Title, Range, Password)

Sub UseChangePassword() 
 
 Dim wksOne As Worksheet 
 
 Set wksOne = Application.ActiveSheet 
 
 ' Protect the worksheet. 
 wksOne.Protect 
 
 ' Establish a range that can allow edits 
 ' on the protected worksheet. 
 wksOne.Protection.AllowEditRanges.Add _ 
 Title:="Classified", _ 
 Range:=Range("A1:A4"), _ 
 Password:="secret" 
 
 MsgBox "Cells A1 to A4 can be edited on the protected worksheet." 
 
 ' Change the password. 
 wksOne.Protection.AllowEditRanges(1).ChangePassword _ 
 Password:="moresecret" 
 
 MsgBox "The password for these cells has been changed." 
 
End Sub

Arguments

The following arguments are required:

Title (String) - The title of the range.

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

The following argument is optional

Password (String) - The password for the range.

AllowEditRanges.Count

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

AllowEditRanges.Item

Returns a single object from a collection.

Syntax : expression.Item (Index)

Index: The name or index number of the object.

Sub UseChangePassword() 
 
 Dim wksOne As Worksheet 
 
 Set wksOne = Application.ActiveSheet 
 
 ' Establish a range that can allow edits 
 ' on the protected worksheet. 
 wksOne.Protection.AllowEditRanges.Add _ 
 Title:="Classified", _ 
 Range:=Range("A1:A4"), _ 
 Password:="secret" 
 
 MsgBox "Cells A1 to A4 can be edited on the protected worksheet." 
 
 ' Change the password. 
 wksOne.Protection.AllowEditRanges.Item(1).ChangePassword _ 
 Password:="moresecret" 
 
 MsgBox "The password for these cells has been changed." 
 
End Sub