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
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