AllowEditRanges.Add Method (Excel)

Office 2013 and later
GitHub-Mark-64px

Contribute to this content

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

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

expression .Add(Title, Range, Password)

expression A variable that represents an AllowEditRanges object.

Parameters

Name

Required/Optional

Data Type

Description

Title

Required

String

The title of range.

Range

Required

Range

Range object. The range allowed to be edited.

Password

Optional

Variant

The password for the range.

Return Value

An AllowEditRange object that represents the range.

This example allows edits to range "A1:A4" on the active worksheet, notifies the user, then changes the password for this specified range and notifies the user of this change.

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