Freeze Panes and Protection

[jbarntish]

Hello,

I’m having a problem with the freeze panes and filter options in Excel 2007 after protecting a worksheet. Here is the code I am using to protect a generated worksheet:

Protection p = ws.Protection;

p.IsDeletingColumnsAllowed = false;

p.IsDeletingRowsAllowed = false;

p.IsEditingContentsAllowed = false;

p.IsEditingObjectsAllowed = false;

p.IsEditingScenariosAllowed = false;

p.IsFilteringAllowed = true;

p.IsFormattingCellsAllowed = true;

p.IsFormattingColumnsAllowed = true;

p.IsFormattingRowsAllowed = true;

p.IsInsertingColumnsAllowed = false;

p.IsInsertingHyperlinksAllowed = false;

p.IsInsertingRowsAllowed = false;

p.IsSelectingLockedCellsAllowed = true;

p.IsSelectingUnlockedCellsAllowed = true;

p.IsSortingAllowed = false;

p.IsUsingPivotTablesAllowed = false;

p.Password = PROTECTION_PASSWORD;

I have filtering allowed set to true, but when the user opens the Excel, the filter option is not available. What am I missing here?

Regarding freeze panes, there doesn’t appear to be a specific item in the list of properties above that enables/disables this option. Is it tied to one of these properties?

Thank you,

Jim

[Amjad_Sahi]

Hi,

Please try the attached version. I have tested your scenario with a template file (The xlsx file contains filters/tables with some frozen panes). When I run your code with my template file using the attached version, it works fine. The filtering is allowed with frozen panes are enabled too.

If you still find the issue, kindly post your template and generated file here, we will check it soon.

Thank you.

[jbarntish]

Hi Amjad,

The attached version of the DLL didn’t seem to change anything. After running some test scenarios, I’ve found that it’s the p.IsEditingContentsAllowed = false; setting that is causing the freeze panes and filter options to be disabled in the worksheet.

Just to be clear on my process:

• I am generating a new .xlsx workbook from scratch with some random data.
• All cells are set to locked.
• There are NO freeze panes or filters dynamically added to the generated worksheet.
• I am protecting the worksheet with the settings provided in the original post.
• I have generated the file under all of these conditions:
• With a license file / without a license file
• I used both the 4.8.2.0 and the 4.8.2.7 dlls.

In all cases, after generation, the freeze panes and filter options in the Excel ribbon are disabled when IsEditingContentsAllowed is set to false.

I can’t speak to what’s actually happening under the hood, but it seems this is a bug. The description of this property states:

"Represents if the user is allowed to edit contents of locked cells on a protected worksheet."

I take this to mean that only the contents of cells cannot be modified. But in fact this setting also disables the freeze panes option in the View tab and the filter option in the Data tab.

Is it the intent of the property to disable freeze panes and filters as well as lock cell contents?

Thanks,

Jim

[Amjad_Sahi]

Hi Jim

Well, p.IsEditingContentsAllowed = false; works as expected.
Actually it refers to the option when you manually click on the Protect Sheet (in Review menu in MS Excel) and check the first option: “Protect worksheet and contents of the locked cells” for a file.

You can confirm in MS Excel manually do your task and you can see that the Freeze Panes in View and Tables in Insert menu are disabled automatically.

Thanks for your understanding!


Well,