Using Macro to turn off cell protection

[jsquareg]

Am running Apache Open Office 4.1.1 under Windows 7. I have posted a description of my problem to a 'Solved' topic elsewhere in this forum but it doesn't seem to have 'taken'. I apologize to the seeming repeat but I am new to the forum and don't know my way around it. Please forgive.

I have tried the Sub shown below and when run as a macro, I get a message saying something like 'Incorrect number of parameters.' I suspect it is because oRange has not been set anywhere and I haven't a clue how go go about setting it. I am very new to Oo's Basic. I want to lock ALL the cells in the active sheet. The workbook is an Excel (xlm) converted to an ods book and I know nothing about using Styles. Would you be kind enough to tell me how to to go about setting oRange to all the cells in a sheet?

Code: Select all

    Sub LockCells(oRange As Object)
rem ----------------------------------------------------------------------
       Dim Doc As Object
       Dim oSheet As Object
       Dim oCell As Object
       Dim p As New com.sun.star.util.CellProtection
       Dim col As Long
       Dim row As Long
          
       Doc = ThisComponent


       oSheet = Doc.Sheets(oRange.RangeAddress.Sheet)
       oSheet.unprotect("")

       For col = 0 to oRange.Columns.Count - 1
          For row = 0 to oRange.Rows.Count - 1
             oCell = oRange.getCellByPosition(col,row)
             p = oCell.CellProtection
             If oCell.getType() <> com.sun.star.table.CellContentType.EMPTY and not p.IsLocked Then
                p.Islocked = True
                oCell.CellProtection = p
             End If
          Next Row
       Next col

       oSheet.protect("")

    End Sub

Thank you very much

[FJCC]

By default, all of the cells are set to be protected if the sheet is protected. To protect the sheet, the code is

Code: Select all

oSheets = ThisComponent.Sheets
oSheet = oSheets.getByName("Sheet1")
oSheet.protect("MyPassword")

You may need to change getByName("Sheet1") to use the name of your sheet and set the password in the protect() command to whatever you want.
You can accomplish the same with the menu Tools -> Protect Document -> Sheet.

[Villeroy]

Same topic as yesterday. viewtopic.php?t=73900

[jsquareg]

Thank you for your help
Yes, I saw your reply and I got that sub and as I said in reply to your answer, it cycles through each sheet and protects it but does not lock the cells. I need to lock/protect the individual cells within the sheet to prevent me from changing any one of them, empty or containg data. I found 'Sub LockCells(oRange As Object)' but can't get it to work. I keep getting a message saying something like 'wrong number of parameters' when I run it. I feel that is due to oRange not being set before running it. Being new to Oo Basic, I've no idea how to set oRange to include all the cells in the active sheet. Any suggestions how to go about that?

I found a sub that will lock all but the empty cells. It works fine except it will let me enter data in an empty cell but that isn't what I need. Can you help me get to where I need to be? Setting the sheet to 'read only' won't solve the problem because it offers to way to unlock an individual cell for editing.

I am sorry I didn't make my need clear. I hope that clarifies my problem.

Thanks again.

[Villeroy]

Make the file read-only.