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

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 ?

lonk gravatar imagelonk ( 2019-10-22 08:26:43 +0200 )edit

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!

Lupp gravatar imageLupp ( 2019-10-22 16:31:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 252 times

Last updated: Sep 25 '18