Protecting cells with XLS Padlock

 

To protect a formula, you just have to right click on one or more cells with the formulas you want to hide and select “Protect selected cell(s) with XLS Padlock” in the mouse’s context menu.

XLS Padlock will then tell you that the cells will be protected.

Overview of protected cells

You can have an overview of all the protected cells by clicking “Protect Formulas” in XLS Padlock tab or menu:

Clicking “Protect Formulas” will open the list of the cells you have configured to be protected. In this window, you can configure the behavior of the protection, remove cell protection or clear the entire list if needed.

When compiling your workbook, XLS Padlock will replace all listed cells with generic PLEvalForm(N) and PLEvalFormD(N, ….) function calls. Your cells remain functional, but end users can’t discover formulas behind. In fact, original formulas do not exist in the compiled workbook anymore: they are managed by the EXE itself.

Cell Dependencies

The Cell Dependencies button lets you control how the protection is applied, two choices being available: “Detect Cell Dependencies” and “No”.

By default, XLS Padlock will detect all cell references and range names in formulas (called cell dependencies) and generate an anonymous function that contains these cell references. Thus, Excel knows how to recalculate protected cells properly. For instance, suppose the formula to protect is = A3^2, XLS Padlock will generate this function: PLEvalFormD(1, COUNT(A3)).

If XLS Padlock fails to protect a cell, you can choose “No” for its “Cell Dependencies” setting. Thus, a simple generic PLEvalForm(N) function will be used.

 

Some complex formulas are not supported by the protection, and will fail (displaying  #Error! or #Value! in the cell). Please test your formulas before distributing your protected workbook. Especially:

 

 

The formula length must be less than 256 characters.

INDIRECT is not supported.

Formulas must not contain any VBA user-defined function, only regular Excel functions.

Formula protection should be used sparingly. Only protect important formulas on which your Excel workbook depends. Protecting several thousands of formulas is not recommended, because it will make your protected XLS file larger and can slow Excel on old computers.

Cell data validation and Conditional Formatting Rules are not supported.

Combining Excel sheet protection and XLS Padlock protection

 


Copyright © 2021 G.D.G. Software