Using Macro to turn off cell protection

Discuss the spreadsheet application

Using Macro to turn off cell protection

Postby jsquareg » Fri Dec 05, 2014 10:08 pm

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   Expand viewCollapse view
    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
Open Office 4.1.1 on Windows 7
jsquareg
 
Posts: 18
Joined: Wed Dec 03, 2014 7:01 pm

Re: Using Macro to turn off cell protection

Postby FJCC » Fri Dec 05, 2014 10:41 pm

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   Expand viewCollapse view
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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7195
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using Macro to turn off cell protection

Postby Villeroy » Fri Dec 05, 2014 10:45 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26862
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Using Macro to turn off cell protection

Postby jsquareg » Fri Dec 05, 2014 11:38 pm

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.
Open Office 4.1.1 on Windows 7
jsquareg
 
Posts: 18
Joined: Wed Dec 03, 2014 7:01 pm

Re: Using Macro to turn off cell protection

Postby Villeroy » Sat Dec 06, 2014 1:41 am

Make the file read-only.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26862
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: robleyd and 14 guests