Lock cell with macro

[Fede1]

Hello!
I would like to lock a cell in order to prevent that the user could write in,
I use the command oCell.cellprotection.isLocked = True/False but don’t run,

Can You help me?

Thank you

Have a nice day

P.S. I tried to use the command:

cp = createUnoStruct(“com.sun.star.sheet.CellProtection”)

Dim cp as new com.sun.star.sheet.CellProtection

but nothing happens

News: I don’t understand whi this code don’t run

Sub test(pPass$, kj#)

oSheet = ThisComponent.getSheets().getbyname("Prova")
oSheet.UnProtect(pPass)
oCellRange = oSheet.GetcellRangebyname("Data")
oCell	= oCellRange.getCellByPosition(2,6)


select case kj

	case 1
		oCell.cellbackcolor = RGB(255,255,0)
		hh = oCell.CellProtection
		hh.IsLocked = False
		oCell.CellProtection = hh
		
	
	case 2
		oCell.clearContents(1)
		oCell.cellbackcolor = RGB(255,255,255)
		hh = oCell.CellProtection
		hh.IsLocked = True
		oCell.CellProtection = hh
	
end select

oSheet.Protect(pPass)

End Sub

[Lupp]

Concerning the “News”.
(Suggestion: Format code more compact for better overview.)
How did you pass the parameter values to the Sub?
Did you run the code step by step in the debugger? Your observations?
What’s your LibO version? Your OS and its version?
Attach “the real thing”: the .ods file (after removing confidential content where applicable).

[Lupp]

-1- You can unprotect a protected cell only if the sheet is unprotected.
-2- It seems the .CellProtection property must be set in one go.
-3- I did not test working with an explicitly created structure.
-4- The following code works for me.

Sub test(Optional pPass As String)
If IsMissing(pPass) Then pPass = "test"
REM In MY testcase I used the second sheet of a document. (API index 1)
theSheet = ThisComponent.Sheets(1)
theSheet.UnProtect(pPass)
a5                = theSheet.GetCellRangeByName("a5")
c5                = theSheet.GetCellRangeByName("c5")
h                 = a5.CellProtection
h.IsLocked        = False
a5.CellProtection = h
h                 = c5.CellProtection
h.IsLocked        = True
c5.CellProtection = h
theSheet.Protect(pPass)
End Sub

[Fede1]

Thank you. Lupp:-D! Only an other question, there is command to pass direcly the password without write it in the code? Thank you

[Lupp]

@Fede1: Do you mean “Can I get the password for a protected sheet evaluating a property?”
If so, the answer is “no”. In the file (persistent representatiion) the password is encrypted,
If you need to remove password protection from a sheet without knowing the password, you have to manipulate the file itself. You cannot do that inside LibO on an opened document without hacking the code.

[lonk]

Dear @Lupp

Sub LockCell
    Dim pPassword As String
	If IsMissing(pPassword) Then pPassword = ""
	oSheet.UnProtect(pPassword)
	Dim oLock As Object
    Dim col, row, i As Integer : row = 6
	For col = 0 To 8
		For i = 0 To row - 1
			oCell = oSheet.GetCellByPosition(col, i)
			Select Case oCell.CellBackColor
				Case -1, 16777175
					oLock                    = oCell.CellProtection
					oLock.IsLocked        = False
					oCell.CellProtection  = oLock
				Case 3433892, 2777241, 5866416, 7512015 ,14608111
					oLock                    = oCell.CellProtection
					oLock.IsLocked        = True
					oCell.CellProtection  = oLock		
			End Select
		Next
	Next		
	oSheet.Protect(pPassword)
End Sub

Why are all blank virgin cells outside A1:J6 locked ?

[Lupp]

CellProtection on is a setting for the default cell style - and you didn’t change anything outside your chosen range by your code.
BTW: Don’t use colours the way you tried. It’s error-prone, and as soon as a palette gets changed, it may be difficult to set the wanted colours again. There are 16 77216 possible values!