Worksheet protection

[Joel_Reinford]

I am trying to protect an Excel worksheet so that the user can select any cell(s), sort any cells, filter any cells but cannot modify the data in any way (delete columns or rows or edit cells.

 

Using API Mate, I come up with this code.

 

    TSheetProtectionOptions SheetProtectionOptions;
    SheetProtectionOptions = new TSheetProtectionOptions(false);
        SheetProtectionOptions.Contents = true;
        SheetProtectionOptions.Objects = true;
        SheetProtectionOptions.Scenarios = true;
        SheetProtectionOptions.SelectLockedCells = true;
        SheetProtectionOptions.SortCellRange = true;
        SheetProtectionOptions.EditAutoFilters = true;
        SheetProtectionOptions.SelectUnlockedCells = true;
    xls.Protection.SetSheetProtection("******", SheetProtectionOptions);

 

However, when I actually create the worksbook, that sheet will not allow sorting or filtering.

 

Is this a bug or am I missing something? We are using Excel 2007 but I have also tested this with Excel 2003 and the results are the same.

[adrian]

Hi,

Have you tried the original file you used for APIMate in Excel itself?

My guess is that you are just running in the normal Excel behavior:

1)Sort is not allowed unless you allow to modify cells, even if you specify "SortCellRange".

 It makes sense, because "cannot modify data in anyway" and "can sort the data" are not compatible. Either the user can modify the data (so he can sort it) or he can't (and won't be able to sort it).

2)The option "Edit Autofilters" means that the user can modify existing autofilters, but he can't create new ones when the sheet is protected. He just can press the arrows of an existing autofilter and change the filter criteria.

This is all how Excel works, we can't change that. I've tried the following code:

       XlsFile xls = new XlsFile(1, true);
        TSheetProtectionOptions SheetProtectionOptions;
        SheetProtectionOptions = new TSheetProtectionOptions(false);
        SheetProtectionOptions.Contents = true;
        SheetProtectionOptions.Objects = true;
        SheetProtectionOptions.Scenarios = true;
        SheetProtectionOptions.SelectLockedCells = true;
        SheetProtectionOptions.SortCellRange = true;
        SheetProtectionOptions.EditAutoFilters = true;
        SheetProtectionOptions.SelectUnlockedCells = true;
        xls.Protection.SetSheetProtection("******", SheetProtectionOptions);

        xls.SetAutoFilter(3, 1, 5);
        xls.Save("r:\\test.xlsx");

And it works fine, it allows you to click in the autofilter arrows, but not add or remove the autofilter itself.

If you can get an xls/x file with Excel that allows you to do something you can't with FlexCel, please send it to me and I'll take a look. But as far as I am aware, what you want to do is not possible in Excel itself.