When working with Excel, it's important to understand how external file permissions (like those set through Windows) and Excel’s internal cell protection features affect your ability to interact with a sheet. This article explores how various file permissions and Excel protection settings behave when files are inserted or shared, ensuring that cell protection is respected where needed without causing unnecessary locking issues.
Windows File Permissions and Excel Protection: No Conflict
Key Point: File permissions added via Windows (such as "Read-only" or "Blocked") do not interfere with Excel’s internal cell protection.
Here are the results of tests performed on different types of files with varied permission settings:
Normal Custom Sheet: When a custom sheet without any special permissions is inserted into Excel, it works perfectly fine. It does not lock the sheet or restrict access in any way.
Blocked Custom Sheet: Inserting a sheet that has been blocked from the Windows level also works seamlessly in Excel. The sheet remains accessible, and no locking occurs.
Read-only Custom Sheet: Even when the file is marked as "Read-only" through Windows, the sheet inserts without issue, and users can still interact with the contents as expected.
Read-only and Blocked Custom Sheet: Similarly, a file that has both "Read-only" and "Blocked" permissions still inserts fine. No locking or blocking occurs within Excel itself.
These tests demonstrate that external permissions like "Read-only" or "Blocked" attributes do not interfere with Excel's ability to manage cell protection. The key takeaway is that Excel continues to handle the sheet and binder (workbook) protection independently of any external file restrictions.
_______________________________________________________________________________________________________________
Cell Protection Within Excel: Honouring Excel’s Native Protection Settings
Excel has its own built-in cell protection system that allows you to lock specific cells or entire sheets to prevent editing. This internal protection is what Excel prioritizes and what Active workpapers honours when inserting sheets from a custom record.
Key Point: When inserting Excel sheets with protected cells (Review > Protect Sheet), Excel retains the original file's protection settings.
A) Fully Protected Sheet: If a custom record's sheet has cells where all cells have been locked and the entire sheet is protected, it will insert just fine. The protection settings are retained, meaning the sheet remains locked, and users cannot edit any of the cells.
B) Semi-Protected/Soft Protected Sheet: In this scenario, some cells are locked while others are unlocked. Excel and Active workpapers honours these settings upon insertion for a custom record.
For example, if you have certain cells marked as locked but others open for editing, Excel will respect this protection structure after the custom sheet is inserted.
_______________________________________________________________________________________________________________
Important Behavior of Excel’s Cell Protection Feature
By default, Excel marks all cells in a worksheet as locked when sheet protection is enabled. This behavior can sometimes cause confusion, especially when creating custom records.
Here’s a demonstration of this behaviour:
i) Default Locking Behaviour: Open a new Excel workbook, click on a blank cell, and inspect its protection settings. You will see that "Locked" is already enabled for all cells when sheet protection is not yet turned on.
ii) Custom Cell Locking Example: In the following example of a custom sheet, if you lock the top cell and leave the bottom cell unlocked, enabling sheet protection with a soft lock with no options selected will result in only the top cell being locked.
Futher, upon uploading this as a custom template:
Clicking on the top cell will show an error message, indicating that the sheet is locked.
Whilst, clicking into the bottom cell, however, will allow you to enter data without any restrictions.
This behavior highlights how Excel’s cell protection works at a granular level, allowing for flexible management of which parts of a sheet can be edited while others remain protected.
Conclusion
1) File permissions applied through Windows, such as "Read-only" or "Blocked," do not interfere with Excel’s internal cell protection settings.
2) Excel prioritizes its own protection mechanisms, meaning that any protection applied to specific cells within Excel will be honored. This is also honoured by us when you upload a custom worksheet as a record.