Recent

Author Topic: Protect cells in WorksheetGrid?  (Read 6419 times)

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Protect cells in WorksheetGrid?
« on: January 25, 2022, 10:27:55 pm »
Hi,

Is it possible to protect cells when using a FFPSpreadsheet WorksheetGrid other than chekcing when a cell has been selected and stopping any chnage if it is a protected cell?

Thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #1 on: January 25, 2022, 11:18:47 pm »
There is built-in protection, and the grid takes care of everything. I just updated the grid documentation with the missing chapter about protection: https://wiki.lazarus.freepascal.org/TsWorksheetGrid#Worksheet_and_cell_protection. You'll also find sample code there.

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #2 on: January 27, 2022, 01:54:28 pm »
Thanks wp,

However, I get a compile error with wsg3year.CellProtection - main_form.pas(163,17) Error: identifier idents no member "CellProtection"
But, wsg3year.Worksheet.Protection := DEFAULT_SHEET_PROTECTION; works fine.
wsg3Year is the name of my WorksheetGrid

I'm using V1.12.0.0 of FSPreadsheet

Thoughts?

Thanks
Dave

« Last Edit: January 30, 2022, 05:44:00 pm by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #3 on: January 30, 2022, 05:43:49 pm »
Hi wp,

Any thoughts onwhy I can't see CellProtection?

Thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #4 on: January 30, 2022, 06:13:53 pm »
Most of the type declarations are in unit fpsTypes. Did you add it to "uses"?

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #5 on: January 31, 2022, 09:37:14 am »
Hi wp,

Yes I have added fpsTypes

Any other thougts,

thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #6 on: January 31, 2022, 10:12:07 am »
You are right, the grid of v1.12 does not yet have this property... Upgrade to the development version on ccr (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/): check out via svn or download the snapshot zip from that site.

But I think I should take the time to prepare another release for OPM although not too many features have been added since v1.12

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #7 on: January 31, 2022, 01:32:04 pm »
Hi wp,

Many thanks again for your help

Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #8 on: January 31, 2022, 07:33:47 pm »
A new release version, v.1.4, is available in OPM now.

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #9 on: January 31, 2022, 09:25:20 pm »
Brills! I'll give it a test, many thanks again
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #10 on: February 01, 2022, 11:29:21 pm »
Hi,

I've installed V1.14.0.0 using OPM and the the attribute is recognised now.
However, I've just had chance to test cell protection with
Code: Pascal  [Select][+][-]
  1. Grid.Worksheet.Protection := DEFAULT_SHEET_PROTECTION;
  2. Grid.Worksheet.Protect(True);
  3. Grid.cellprotection[1,21]:=[];
but it doesn't seem to work, i.e. I can edit the cell, have I missed something
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #11 on: February 02, 2022, 12:13:44 am »
As I wrote in the wiki, that entire protection thing follows the concept of Excel etc: When you protect a sheet, NO cell can be changed. And you must remove the cell protection to make a specific cell writeable. It's the other way round... When you set CellProtection of cell [1,21] to an empty set you remove the cpLockCell element, and the cell can be written. If you omit the line with CellProtection the cell cannot be written - but no other cell can be written either.

That's the idea of Excel: block the entire sheet, and give access only to the cells in which the user is supposed to enter something. For a long time I had problems to understand why they do it this way. But basically, it is the correct way to do it: the majority of cells contains formulas, titles, captions etc and they should not be changeable by the user; only a few cells need to be accessible for user input.

The opposite logic, i.e. to have the entire sheet writeable, but block input only for a few cells, is not supported by concept. Only if you have a sheet limited in size then you can allow write access to all cells except for [1,21] by iterating over them and setting their CellProtection to [].

Code: Pascal  [Select][+][-]
  1. var
  2.   r, c: Integer;
  3. ...
  4.   for r :=0 to (lastRow) do
  5.     for c := 0 to (lastCol) do
  6.       if not ((c = 1) and (r = 21)) then
  7.         Grid.CellProtection[r, c] := [];


daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Protect cells in WorksheetGrid?
« Reply #12 on: February 02, 2022, 10:32:50 am »
Hi wp,

Thanks for the guidance and having working with Excel for many years, I do understand the raionale of why they do it the way they do. As you say, in the majority of cases, when protection is important, in my experience you want to lock the entire sheet and then unlock what the user can edit.

I thought I'd got that with setting the worksheet to locked and then clearing the cell I was interested in.

I followed your sample code from the on-line help
Quote
Code: Pascal  [Select][+][-]
  1.   //Protect the worksheet
  2.   Grid.Worksheet.Protection := DEFAULT_SHEET_PROTECTION;
  3.   Grid.Worksheet.Protect(Checkbox1.Checked);
  4.  
  5.   // Unprotect the writeable cells A1 and C2:C5
  6.   Grid.CellProtection[1, 1] := [];
  7.   Grid.CellProtections[3, 2, 3, 5] := [];
which I thought would lock the worksheet and then unlock a particular cell? I assumed that Checkbox1.Checked allowed you to switch on and off worsheet protection through a checkbox, but I just set it to True.

Have I missed somethiing?
And could you point me to where I can find the values for CellProtect and WorksheetProtection such as DEFAULT_SHEET_PROTECTION?

Thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #13 on: February 02, 2022, 11:07:58 am »
Have I missed somethiing?
I don't think so. Find a working example in the attachment

And could you point me to where I can find the values for CellProtect and WorksheetProtection such as DEFAULT_SHEET_PROTECTION?
This is from unit fpsTypes:
Code: Pascal  [Select][+][-]
  1. type
  2.   {@@ Workbook protection options }
  3.   TsWorkbookProtection = (bpLockRevision, bpLockStructure, bpLockWindows);
  4.   TsWorkbookProtections = set of TsWorkbookProtection;
  5.  
  6.   {@@ Worksheet protection options. All used items are locked. }
  7.   TsWorksheetProtection = (
  8.     spFormatCells, spFormatColumns, spFormatRows,
  9.     spDeleteColumns, spDeleteRows,
  10.     spInsertColumns, spInsertRows, spInsertHyperlinks,
  11.     spCells, spSort, spObjects,
  12.     spSelectLockedCells, spSelectUnlockedCells
  13.     {spPivotTables, spScenarios }
  14.   );
  15.   TsWorksheetProtections = set of TsWorksheetProtection;
  16.  
  17.   {@@ Cell protection options }
  18.   TsCellProtection = (cpLockCell, cpHideFormulas);
  19.   TsCellProtections = set of TsCellProtection;
  20.  
  21. const     // all this actions are FORBIDDEN if included and ALLOWED if excluded!
  22.   ALL_SHEET_PROTECTIONS = [spFormatCells, spFormatColumns, spFormatRows,
  23.     spDeleteColumns, spDeleteRows, spInsertColumns, spInsertRows, spInsertHyperlinks,
  24.     spCells, spSort, spObjects, spSelectLockedCells, spSelectUnlockedCells
  25.     {spPivotTables, spScenarios} ];
  26.  
  27.   DEFAULT_SHEET_PROTECTION = ALL_SHEET_PROTECTIONS - [spSelectLockedCells, spSelectUnlockedcells];
  28.  
  29.   DEFAULT_CELL_PROTECTION = [cpLockCell];

wp

  • Hero Member
  • *****
  • Posts: 11853
Re: Protect cells in WorksheetGrid?
« Reply #14 on: February 02, 2022, 01:11:34 pm »
Sorry, I forgot the attachment in the previous post. Maybe it gets clearer with that demo.

 

TinyPortal © 2005-2018