Ask Your Question
0

Lock cell with macro

asked 2018-09-10 11:09:45 +0200

Fede gravatar image

updated 2018-09-25 11:35:32 +0200

Lupp gravatar image

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
edit retag flag offensive close merge delete

Comments

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 gravatar imageLupp ( 2018-09-25 11:39:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-09-10 14:10:10 +0200

Lupp gravatar image

-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
edit flag offensive delete link more

Comments

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

Fede gravatar imageFede ( 2018-09-10 14:58:33 +0200 )edit

@Fede: 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.

Lupp gravatar imageLupp ( 2018-09-10 17:35:03 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-10 11:09:45 +0200

Seen: 92 times

Last updated: Sep 25 '18