How to: Protect Specific Worksheet Ranges

• Mar 20, 2020
• 2 minutes to read

This example demonstrates how to unlock specific ranges in a protected worksheet for authenticated users.

The ProtectedRangeCollection collection (accessible using the Worksheet.ProtectedRanges property) contains ranges that can be unlocked for users providing the required credentials. A user can be authenticated in one of the following ways.

• Authentication that uses the user account under which the application runs. The authentication itself is performed by Windows based on the standard security mechanisms that rely on domain security.

  To unlock a range for a specific user or user group, perform the following steps.

  1. Create an EditRangePermission object for each user or group.
  2. Transform a list of permissions to a security descriptor by using the ProtectedRange.CreateSecurityDescriptor method.
  3. Assign the created security descriptor to the range by using the ProtectedRange.SecurityDescriptor property.
• Authentication that uses a password. To specify a password for the range, use the ProtectedRange.SetPassword method. When an attempt is made to edit the range, the user will be prompted for a password.

The code example below illustrates both approaches.

View Example

ProtectionActions.cs

Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
worksheet["B2:J5"].Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin);

// Specify user permission to edit a range in a protected worksheet.
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["B2:J5"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = Environment.UserName;
permission.DomainName = Environment.UserDomainName;
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("123");
// Protect a worksheet.
if (!worksheet.IsProtected)
    worksheet.Protect("password", WorksheetProtectionPermissions.Default);
// Add a note.
worksheet["B2"].Value = "This cell range is protected with a password. \n You cannot edit or format it until protection is removed." +
                        "\nTo remove protection, double-click the range and enter \"123\".";
worksheet.Visible = true;

ProtectionActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
worksheet("B2:J5").Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin)

' Specify user permission to edit a range in a protected worksheet.
Dim protectedRange As ProtectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet("B2:J5"))
Dim permission As New EditRangePermission()
permission.UserName = Environment.UserName
permission.DomainName = Environment.UserDomainName
permission.Deny = False
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(New EditRangePermission() { permission })
protectedRange.SetPassword("123")
' Protect a worksheet.
If Not worksheet.IsProtected Then
    worksheet.Protect("password", WorksheetProtectionPermissions.Default)
End If
' Add a note.
worksheet("B2").Value = "This cell range is protected with a password. " & ControlChars.Lf & " You cannot edit or format it until protection is removed." & ControlChars.Lf & "To remove protection, double-click the range and enter ""123""."
worksheet.Visible = True