xlsgen > overview > Protected Ranges

Protected ranges was introduced with Excel 2003 in order to make it possible to create ranges editable only for a restricted set of persons in your organization or by filling in a password before they are able to do so. As the name implies, protected ranges apply to cell areas, not the entire worksheet, which can be very useful in a number of scenarios.

If you are using Excel, protected ranges appear in the user interface with the Review ribbon tab and by clicking to "Allow users to edit ranges". That brings a dialog box for someone to name and identify a range and then apply to it a password optionally, and also name users or groups to restrict who can edit the cells in that range.

Protected ranges take effect when the worksheet itself is protected. So essentially protected ranges are editable ranges inside a protected worksheet.

xlsgen exposes the programming interface to create, edit and delete protected ranges. And that works with all Excel versions which means XLS files (and their variants) and XLSX files (and their variants, including XLSB among them).

Creating a protected range

Creating a protected range is actually very simple. Take a regular range, made of one or more cell areas, and invoke the ProtectedRange method call and you are done.

A protected range interface exposes a number of properties :

• name. Required. Automatically created.
• range. Required. Automatically created.
• password. Optional. When set, a password must be entered by the user for editing cells in the range.
• users and groups. Optional. Those are Windows domain-based names which are allowed to make edits in the range.

// create a protected range
xlsgen::IXlsProtectedRangePtr pr = worksheet->NewRange(L"B11:D14")->ProtectedRange;

// set the password and the names of those allowed to make edits
pr->Password = L"aaa";
pr->AddUserOrGroup(L"john");
pr->AddUserOrGroup(L"mary");

// remember to protect the worksheet
worksheet->LockProperties->Content = TRUE;

Enumerating and deleting a protected range

Existing protected ranges can be enumerated for inspection. A worksheet exposes the ProtectedRanges interface for this.

Once a protected range instance is known, it can be inspected and it can be deleted.

// delete the third protected range
worksheet->ProtectedRanges->Item[3]->Delete();