How to: Protect Specific Worksheet Ranges

• Jan 23, 2019
• 2 minutes to read

You can unlock specific ranges in a protected worksheet for the end-user who provide the required credentials. When an end-user attempts to edit a cell in the range, the Spreadsheet control sends a request to identify the user. The end-user may be prompted for a password, or can be silently authenticated with the Windows Active Directory services by the user account under which the spreadsheet application runs. If two mentioned options are specified for the same range, the Active Directory services are queried first, and if authentication fails, a password dialog is invoked.

To apply range protection, create a ProtectedRange object for the required range.

To use password authentication, specify the password using the ProtectedRange.SetPassword method.

To use Windows Active Directory services, create an EditRangePermission object for each user or group, transform a list of permissions to a security descriptor by using the ProtectedRange.CreateSecurityDescriptor method and specify the security descriptor for the range by using the ProtectedRange.SecurityDescriptor method.

The following code illustrates both options.

The entire worksheet is protected with a “password” password. End-users can only select cells.

On an attempt to edit range “C3:E8” on a protected sheet, the end-user will be prompted for a password (“123”) unless the application which loaded the worksheet is not run under the same account as the application which has executed the following code snippet. If it is so, a password is not required to edit the range “C3:E8”.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/winforms-spreadsheetcontrol-api-part-2-e4832

ProtectionActions.cs

workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
worksheet["C3:E8"].Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin);

// Give specific user permission to edit a range in a protected worksheet. 
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["C3:E8"]);
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);

worksheet.ActiveView.ShowGridlines = false;
workbook.EndUpdate();

ProtectionActions.vb

workbook.BeginUpdate()
Dim worksheet As Worksheet = workbook.Worksheets(0)
worksheet("C3:E8").Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin)

' Give specific user permission to edit a range in a protected worksheet. 
Dim protectedRange As ProtectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet("C3:E8"))
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

worksheet.ActiveView.ShowGridlines = False
workbook.EndUpdate()