How to: Protect Specific Worksheet Ranges
- 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”.
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();