UserImage.jpg
Fabien Schmitt
44Views
7Comments
Solved
[Advanced Excel] Remove protection of cells (and other general formatting options)
Question
Extension
Forge
advanced-excel
Service icon
Advanced Excel
Forge component by Carlos Freitas
Application Type
Service

Hi,

When using Cell_FormatRange, if we want to make a cell available for editing in a protected workbook, it won't be set as per following lines (in ApplyFormatToRange function):

 if (format.ssSTCellFormat.ssLocked)
            {
                range.Style.Locked = format.ssSTCellFormat.ssLocked;
            }

As locking default is already true on every cells, you never get a chance to set it to false, making the whole cells still protected when calling Worksheet_Protect.


As well, for other properties, you get the chance to set something, but never remove it, for instance, if you make a cell bold, it will stay bold.

IMO, it would be better to separate all kind of formatting, avoiding to have defaults taking priority when nothing is set for that property, and making clear about what you want to do

1
0
13 Sep
2022-07-06 03-36-19
Hanno
 
MVP
Solution

Thanks for the great feedback, @Fabien Schmitt 

Appreciation of effort goes a long way ;) 

On point 1 I have some immediate thoughts that I'll investigate.

On point 2, it may be related to an issue I was looking at a few years ago with attempting to expand worksheet protection to allow more granular control

Hanno

See solution in context
0
0
02 Nov
2022-07-06 03-36-19
Hanno
 
MVP

Hi @Fabien Schmitt 

If you set the Locked attribute to false when using CellFormat_ApplyToRange, does that not solve your issue? Yes, there is a default value but you are able to set it to false.

0
0
10 Oct

Hi @Hanno,

Thanks for your feedback, but it's actually what we tried. All cells are locked by default in Excel, but when you try to unlock it by setting the "Locked" property to "false", the extension won't do anything as per ApplyFormatToRange in Util.cs:

 if (format.ssSTCellFormat.ssLocked)
            {
                range.Style.Locked = format.ssSTCellFormat.ssLocked;
            } 

Here, range.Style.Locked can't be changed as the condition is true only when you want to lock a cell, thus, it's impossible to unlock a cell that way.

Is there any other way to do it?

0
0
10 Oct
2022-07-06 03-36-19
Hanno
 
MVP

Replying to Fabien Schmitt's comment on 10 Oct 2023 16:20:09

Hmmm. Right, I see now.

I'll review that entire function and update soon.

Hanno

0
0
10 Oct
2022-07-06 03-36-19
Hanno
 
MVP

Hi @Fabien Schmitt 

I have refactored the code based on your suggestion.

There is also a working sample in the demo app (Sample04) that shows how to protect the sheet but have some cells unlocked and allow the user to interact with those cells.

Hope this helps.

Hanno

1
0
22 Oct

Hi @Hanno,

Thanks for the implementation, I'm now able to protect the sheet as needed, just 2 remarks:

  1. For reference, so people don't chase a non existing bug, now, if you apply twice a format on a cell and leaving the value empty for following parameters at second time, they will be reset (for instance, if you set bold first, and apply something else without putting true in bold, it won't be bold anymore):
    • Bold
    • WrapText
    • TextRotation
    • ShrinkToFit
    • ReadingOrder
    • QuotePrefix
    • Locked
    • Indent
    • Hidden

  2. A little bug (with a workaround): there is a password value under ProtectedOptions, this one won't work, you need to use the one above:

    In the code, the wrong property is used (copy/paste error as it seems ^^):

    if (!string.IsNullOrEmpty(ssPassword))            {
                    ws.Protection.SetPassword(ssPassword);
                }
                else
                {
    if (ssProtectionOptions != null && !string.IsNullOrEmpty(ssProtectionOptions.ssSTProtection.ssPassword))
                    {
    ws.Protection.SetPassword(ssPassword); // not the correct property
                    }
                }

    Seems you could get rid of it, or maybe you left it for compatibility with older version?

    Anyway, thanks for the great support!
0
0
01 Nov
2022-07-06 03-36-19
Hanno
 
MVP
Solution

Thanks for the great feedback, @Fabien Schmitt 

Appreciation of effort goes a long way ;) 

On point 1 I have some immediate thoughts that I'll investigate.

On point 2, it may be related to an issue I was looking at a few years ago with attempting to expand worksheet protection to allow more granular control

Hanno

0
0
02 Nov

Ok Hanno, pretty sure you'll have a look on that 2 points which are indirectly related to my point, so I already marked your changes as a solution as I'm already able to work with it

Thanks and have a good day!

1
0
02 Nov
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.