Ask Your Question

BASIC|Calc: Protect cells

asked 2019-10-17 06:11:33 +0100

lonk gravatar image

updated 2019-10-17 06:13:58 +0100

I used the code from here.

Sub lock_sheets
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
    cp_locked.islocked = true
    dim cp_unlocked as new
    cp_unlocked.islocked = false
        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
End Sub

The results:

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

All cells with "Hello !" are locked as required.

image description

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

image description

edit retag flag offensive close merge delete


Dear @pierre-yves samyn ,

Can you please help ?

lonk gravatar imagelonk ( 2019-10-17 06:48:39 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-10-20 10:37:35 +0100

pierre-yves samyn gravatar image


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.)


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-17 06:11:33 +0100

Seen: 40 times

Last updated: Oct 20