BASIC|Calc: Protect cells

[lonk]

I used the code from here.

Sub lock_sheets
	lock_nonempty_cells(0)
	lock_nonempty_cells(1)
End Sub

Sub lock_nonempty_cells(shindex As Integer)
	Dim sh, oFilled, oBlanks As Object
	Dim iWhat As Integer
	Const cPWD = ""
	dim cp_locked as new com.sun.star.util.CellProtection
	cp_locked.islocked = true
	dim cp_unlocked as new com.sun.star.util.CellProtection
	cp_unlocked.islocked = false
	sh=ThisComponent.Sheets.getByIndex(shindex)
	sh.unProtect(cPWD)
	With com.sun.star.sheet.CellFlags
		iWhat= .VALUE + .DATETIME + .STRING + .FORMULA
	End With
	oFilled = sh.queryContentCells(iWhat)
	oBlanks = sh.queryEmptyCells()
'	isLocked is read only isLocked=true will not work. New object needs to be assigned to CellProtection
	oBlanks.CellProtection = cp_unlocked
	oFilled.CellProtection = cp_locked
	sh.protect(cPWD)
End Sub

The results:

Sheet(0) is perfect, all blank cells can be modified as required.

All cells with “Hello !” are locked as required.

But in Sheet(1), why can’t some blank cells ( C2:I5, A26:E35, J4:J35 and K2:XXXX35) be modified ?

[lonk]

Dear @PYS ,

Can you please help ?

[PYS]

Hi

Hard to say with only screenshots…

One hypothesis could be a direct formatting/style conflict. Anyway, I wouldn’t recommend this way of doing things. I always prefer the application of styles.

In this case I do not share the point of view of developers who have enabled protection for default style. I prefer to do so:

• change the default style to uncheck the protection
• create of a “Protected” style activating protection
• apply this style on the cells to be protected.

Indeed, in my opinion, a spreadsheet is by default a tool in which to enter and, by exception to protect (formulas for example). That said, you don’t have to be psycho-rigid: if the sheet is mainly to be protected with a few free cells, you can reverse the reasoning.

Other advantages of the application by style:

• simplified programming (you just apply a style)
• possibility of enhancement (dedicated colours, fonts, etc.)

Regards