[Solved] Turn on/off cell protection using BASIC macro
[Solved] Turn on/off cell protection using BASIC macro
Hello,
Have some troubles with Calc regarding "data safety".
I think the solution will be helpfull to many of Calc users.
I'll keep it short.
I use OpenOffice 3.2.1 on WinXP/WinVista/Win7/Ubuntu 10.04.
1. A calc document contains 1 sheet. The doc and the sheet are password protected.
2. The user opens the doc and types in the password.
3. Initially all cells in the sheet are protected, so the user can't make any changes unless he allows macros to be used.
4. If he does, a macro called OpenSession is invoked right after the stage 2.
NewSession macro turns off protection of all the non-empty cells within the given area.
This causes the user to have an ability to enter new data in these cells.
5. The user enters new data, saves and cloese the document or he clicks the close button („X”) of the window and agrees to store unsaved data.
6. Upon document closing another macro called CloseSession is executed. It's crucial task is to turn on protection for every cell in the sheet.
The secondary task of CloseSession macro is do some data cleaning. It means that i.e. if the user didn't filled in all the mandatory cells in a row, the whole row will be cleared from all content.
Main questions is:
Q1: How do you turn on/off protection of a given cell or cell area using a BASIC macro instruction?
My other questions:
Q2: Is it possible to protect cell's format from being changed by the user while he can modify the content of that cell at the same time?
Q3: Is there a key shortcut I can use to open a listbox that apears in selected cell after applying Menu->Data->Data verification->List?
Please help.
Thank you in advance.
Cheers,
Darius/Poland
Have some troubles with Calc regarding "data safety".
I think the solution will be helpfull to many of Calc users.
I'll keep it short.
I use OpenOffice 3.2.1 on WinXP/WinVista/Win7/Ubuntu 10.04.
1. A calc document contains 1 sheet. The doc and the sheet are password protected.
2. The user opens the doc and types in the password.
3. Initially all cells in the sheet are protected, so the user can't make any changes unless he allows macros to be used.
4. If he does, a macro called OpenSession is invoked right after the stage 2.
NewSession macro turns off protection of all the non-empty cells within the given area.
This causes the user to have an ability to enter new data in these cells.
5. The user enters new data, saves and cloese the document or he clicks the close button („X”) of the window and agrees to store unsaved data.
6. Upon document closing another macro called CloseSession is executed. It's crucial task is to turn on protection for every cell in the sheet.
The secondary task of CloseSession macro is do some data cleaning. It means that i.e. if the user didn't filled in all the mandatory cells in a row, the whole row will be cleared from all content.
Main questions is:
Q1: How do you turn on/off protection of a given cell or cell area using a BASIC macro instruction?
My other questions:
Q2: Is it possible to protect cell's format from being changed by the user while he can modify the content of that cell at the same time?
Q3: Is there a key shortcut I can use to open a listbox that apears in selected cell after applying Menu->Data->Data verification->List?
Please help.
Thank you in advance.
Cheers,
Darius/Poland
Last edited by Hagar Delest on Fri Oct 01, 2010 11:53 am, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
Re: Turn on/off cell protection using BASIC macro
You are aware that protection is not even child proof? It's just a lock against casual mistakes. Anyone can turn it off easily by editing the content.xml.
You are aware how protection works in general? All the cells are locked by default. When you protect the sheet, the locks become active.
No, you can not protect formatting.
Install the MRI extension and find the required services and interfaces.
What you try to do looks like a typical Excel/VBA application. I would never do such things again.
You are aware how protection works in general? All the cells are locked by default. When you protect the sheet, the locks become active.
No, you can not protect formatting.
Install the MRI extension and find the required services and interfaces.
What you try to do looks like a typical Excel/VBA application. I would never do such things again.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Turn on/off cell protection using BASIC macro
Hello.Villeroy wrote:You are aware that protection is not even child proof? It's just a lock against casual mistakes. Anyone can turn it off easily by editing the content.xml.
You are aware how protection works in general? All the cells are locked by default. When you protect the sheet, the locks become active.
No, you can not protect formatting.
Install the MRI extension and find the required services and interfaces.
What you try to do looks like a typical Excel/VBA application. I would never do such things again.
I only want to make sure the user won't accidently mess up the existing data in the sheet while entering new content.
The data isn't secret. It's also present in the paper form and the user has full access to it. The sheet is just a helper to calculate the values. The "safety" means here not to have ability to mess up the data the user or someone else already entered (he might break it because of his inexperience with spreadsheets) and not to modify sheet's format structure, because it's also used to make printouts.
Listen, it looks like this. The guy has this register with some dates, parameters and values he enters on a daily basis. After a month he has to make a report including total sums, averages, maximums, etc. So it's very helpfull to have this kind of data in spreadsheet form. They (the company he and I work in) have MS Office with Excel but frankly, I hate the M$ and all of it's products, so I chose to make all documents in OpenOffice. Honestly, the BASIC programming wasn't even needed to this point.
So he (the user) has to add up some values he brings out using simple (standard) data filtering.
He asked me to make him a simple table sheet where he can store all the raw data.
The guy knows what spreadsheet is. At least he told me so. But when it comes to the usage of some of the spreadsheet's functions, he sometimes ends up confused and it happened two or three times he accidently overwritten some data. He didn't even know that, and then the reports were giving him bad results.
Until he totally understands how Calc works, I want him to be 100% sure that the data he already has in the sheet won't get effed up.
I thought then that I create a sheet, in which he can modify empty cells as he wish, but not the existing data he left in the sheet i.e. a day before.
The instruction he understans now goes like this:
1. You open the sheet and enter some records (data in rows).
2. You make sure everything you entered is correct.
3. If somethings wrong, change it.
4. But if all is o.k., you can close the document, but remember, you must be sure you entered all data correctly, because after you close the document you won't be able to modify these data.
In other case, you will then have to call me and ask me for help. Although I'm right there, in the office near by, so I can come relatively quickly, but I won't always have the time or the will to do so =)
4. After you close the document, you can open it again and see that everything you left in it last time is locked and you cannot change it, and if you made some changes to the cells format - look, they all look as they should again.
The thing is, that I've created the sheet that will be usefull with all of the data in the future to the other users, and I don't want the user to mess up the data and the formatting.
I used a couple of Menu->Data->Data verification->List to create list choices and I don't want the user to break that.
I know he can do that if the cell isn't protected, but than if he modifies the format I can make a macro that will copy the proper format to that cell, making it look and work as it should.
I know I could make a form for data input, but programming forms become too much time-consuming when you want to have a simple table and use the standard filters to bring out some sums, averages, extremums and such. And frankly, as much as I love Openoffice in general, I hate OO forms. Programming forms in OO is just to complicated, and the help system doesn't really bring that much of help.
Still, I want to use Calc, but I need the macro.
So, the main question remains.
Is "locked" or "protected" property of the given cell even available (changable) in BASIC?
What would be the proper BASIC equivalent to such instructions:
Code: Select all
FOR all cells IN the given area DO
IF the cell isn't locked and the cell isn't empty THEN lock the cell
Can it be done?
That's all I ask.
Cheers,
Darius
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Turn on/off cell protection using BASIC macro
Hastily adapted and not carefully tested, but I'm sure what you want can be done. For the cells in oRange:
Code: Select all
Sub LockCells(oRange As Object)
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 = oRange.RangeAddress.Sheet
oSheet.unprotect("Password")
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("Password")
End Sub
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Turn on/off cell protection using BASIC macro
Thanks. I needed that "IsLocked" thing. Also I didn't know about the MRI extension. I can't figure out how or where to use it, but it seems potentially helpfull =)Charlie Young wrote:Hastily adapted and not carefully tested, but I'm sure what you want can be done. For the cells in oRange:
Code: Select all
Sub LockCells(oRange As Object) 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 = oRange.RangeAddress.Sheet oSheet.unprotect("Password") 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("Password") End Sub
These instruction above doesn't work for me, though, but at least I got some hint about accessing the "locked" property of the cell. I'll try that now and come back later.
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: Turn on/off cell protection using BASIC macro
I'm not sure why it doesn't work for you, but the code does contain a serious blunder.
oSheet = oRange.RangeAddress.Sheet
Should be
oSheet = Doc.Sheets(oRange.RangeAddress.Sheet)
oSheet = oRange.RangeAddress.Sheet
Should be
oSheet = Doc.Sheets(oRange.RangeAddress.Sheet)
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: Turn on/off cell protection using BASIC macro
Hi,Charlie Young wrote:I'm not sure why it doesn't work for you, but the code does contain a serious blunder.
oSheet = oRange.RangeAddress.Sheet
Should be
oSheet = Doc.Sheets(oRange.RangeAddress.Sheet)
well, it does work (after some tweaking), but it takes too much time (like 20-30 secs) to finish the job on area A1:I1001.
Can I somehow inform the user that the macro is launched and force him to wait so the macro could safely process all data?
I ask because if I make it run upon closing the document and then I close the Calc window ie. using X in the corner, I have to wait that couple of seconds for the window to actually close, but when I click X again while the data is being processed by the macro, the Calc always crashes. Of course not all cells are then processed, so the whole macro deal becomes useless.
How to avoid it?
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
Re: Turn on/off cell protection using BASIC macro
This makes use of the OpenOffice.org-API and works instantly with a cell style "Unlocked":
Same stuff in Python:
Code: Select all
REM ***** BASIC *****
Sub Main
Const cPWD = ""
Const cLockedStyle ="Default"
Const cUnLockedStyle = "Unlocked"
sh=ThisComponent.Sheets.getByIndex(0)
sh.unProtect(cPWD)
With com.sun.star.sheet.CellFlags
iWhat= .VALUE + .DATETIME + .STRING + .FORMULA
End With
oFilled = sh.queryContentCells(iWhat)
oBlanks = sh.queryEmptyCells()
oBlanks.CellStyle=cUnlockedStyle
oFilled.CellStyle= cLockedStyle
sh.protect(cPWD)
End Sub
Code: Select all
import uno
from com.sun.star.sheet.CellFlags import VALUE,DATETIME,STRING,FORMULA
cWhat = VALUE+DATETIME+STRING+FORMULA
cPWD = ""
cLockedStyle ="Default"
cUnLockedStyle = "Unlocked"
def switchLock:
sh=ThisComponent.Sheets.getByIndex(0)
sh.unProtect(cPWD)
oFilled = sh.queryContentCells(iWhat)
oBlanks = sh.queryEmptyCells()
oBlanks.CellStyle=cUnLockedStyle
oFilled.CellStyle=cLockedStyle
sh.protect(cPWD)
g_exportedScripts = switchLock,
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Turn on/off cell protection using BASIC macro
My God, you're genius =)Villeroy wrote:This makes use of the OpenOffice.org-API and works instantly with a cell style "Unlocked":Same stuff in Python:Code: Select all
REM ***** BASIC ***** Sub Main Const cPWD = "" Const cLockedStyle ="Default" Const cUnLockedStyle = "Unlocked" sh=ThisComponent.Sheets.getByIndex(0) sh.unProtect(cPWD) With com.sun.star.sheet.CellFlags iWhat= .VALUE + .DATETIME + .STRING + .FORMULA End With oFilled = sh.queryContentCells(iWhat) oBlanks = sh.queryEmptyCells() oBlanks.CellStyle=cUnlockedStyle oFilled.CellStyle= cLockedStyle sh.protect(cPWD) End Sub
Code: Select all
import uno from com.sun.star.sheet.CellFlags import VALUE,DATETIME,STRING,FORMULA cWhat = VALUE+DATETIME+STRING+FORMULA cPWD = "" cLockedStyle ="Default" cUnLockedStyle = "Unlocked" def switchLock: sh=ThisComponent.Sheets.getByIndex(0) sh.unProtect(cPWD) oFilled = sh.queryContentCells(iWhat) oBlanks = sh.queryEmptyCells() oBlanks.CellStyle=cUnLockedStyle oFilled.CellStyle=cLockedStyle sh.protect(cPWD) g_exportedScripts = switchLock,
If only there was a doc, a tutorial, a book or something explaining these things to the beginner with real examples.
Ok... I did notice one thing using instructions you all provided. Even if a cell is locked and the sheet is password protected, using macro you can still change that cell's properties and content without the need to unlock the sheet. Isn't this a bug of somekind or is it intended?
I just skip the protect/unprotect entries and I think, it still works as expected.
But of course I understand the logic behind unprotecting the sheet before making any changes and analogically - protecting it after the change has been made. I wanted that in the first place =P
Your script, Villeroy is so much simpler and faster. Is there a tutorial of somekind that could explain the basics of how to operate on cells, cell areas, sheets the way you did - using OpenOffice API?
All the tutors I can find in the net explain the "classic" (slow) methods of modifying cell structure and content.
I will now try to customise your example to meet my needs.
Cheers,
D
Again, thank you.
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
Re: [Solved] Turn on/off cell protection using BASIC macro
development.openoffice.org
http://api.openoffice.org/docs/common/r ... le-ix.html
http://extensions.services.openoffice.o ... roject/MRI
EDIT:
The query functions from interfaces XCellRangesQuery, XFormulaQueryand XCellRangesQuery always return a SheetCellRanges collection (enumeration of cell rectangles, including sheets and single cells).
One issue with my code: It makes no sense to change the protection by means of styles. I think hard formatting regardless of cell style is the way to go in this praticular case.
oBlanks.CellProtection=False
oFilled.CellProtection=True
There is another issue with cells having an annotation only (c.s.s.sheet.CellFlags.ANNOTATION). They are not touched by the above macro. The CellFlags constants are badly organized. I don't know any way how to treat blanks with annotations as empty cells. Add the .ANNOTATION flag to nWhich in order to add them to the oFilled collection.
http://api.openoffice.org/docs/common/r ... le-ix.html
http://extensions.services.openoffice.o ... roject/MRI
EDIT:
The query functions from interfaces XCellRangesQuery, XFormulaQueryand XCellRangesQuery always return a SheetCellRanges collection (enumeration of cell rectangles, including sheets and single cells).
One issue with my code: It makes no sense to change the protection by means of styles. I think hard formatting regardless of cell style is the way to go in this praticular case.
oBlanks.CellProtection=False
oFilled.CellProtection=True
There is another issue with cells having an annotation only (c.s.s.sheet.CellFlags.ANNOTATION). They are not touched by the above macro. The CellFlags constants are badly organized. I don't know any way how to treat blanks with annotations as empty cells. Add the .ANNOTATION flag to nWhich in order to add them to the oFilled collection.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Turn on/off cell protection using BASIC macro
I've used slightly modified version of Villeroy's macro which works ok.
Thank you for posting this.
Thank you for posting this.
Code: Select all
Sub lock_sheets
lock_nonempty_cells(1)
lock_nonempty_cells(2)
lock_nonempty_cells(3)
End Sub
Sub lock_nonempty_cells(shindex As Integer)
Const cPWD = ""
dim cp_locked as new com.sun.star.util.CellProtection
cp_locked.islocked = true
dim cp_unlocked as new com.sun.star.util.CellProtection
cp_unlocked.islocked = false
sh=ThisComponent.Sheets.getByIndex(shindex)
sh.unProtect(cPWD)
With com.sun.star.sheet.CellFlags
iWhat= .VALUE + .DATETIME + .STRING + .FORMULA
End With
oFilled = sh.queryContentCells(iWhat)
oBlanks = sh.queryEmptyCells()
rem isLocked is read only isLocked=true will not work. New object needs to be assigned to CellProtection
oBlanks.CellProtection = cp_unlocked
oFilled.CellProtection = cp_locked
sh.protect(cPWD)
End Sub
LibreOffice 3.5 on Ubuntu 12.04
Re: Turn on/off cell protection using BASIC macro
I have tried the Sub 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?
Thank you very much
Thank you very much
wrrr wrote:Thanks. I needed that "IsLocked" thing. Also I didn't know about the MRI extension. I can't figure out how or where to use it, but it seems potentially helpfull =)Charlie Young wrote:Hastily adapted and not carefully tested, but I'm sure what you want can be done. For the cells in oRange:
Code: Select all
Sub LockCells(oRange As Object) 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 = oRange.RangeAddress.Sheet oSheet.unprotect("Password") 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("Password") End Sub
These instruction above doesn't work for me, though, but at least I got some hint about accessing the "locked" property of the cell. I'll try that now and come back later.
Open Office 4.1.1 on Windows 7
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
can anyone point me to a way of calling such a macro from the command line?sm00th wrote:I've used slightly modified version of Villeroy's macro which works ok.
Thank you for posting this.
Code: Select all
Sub lock_sheets lock_nonempty_cells(1) lock_nonempty_cells(2) lock_nonempty_cells(3) End Sub Sub lock_nonempty_cells(shindex As Integer) Const cPWD = "" dim cp_locked as new com.sun.star.util.CellProtection cp_locked.islocked = true dim cp_unlocked as new com.sun.star.util.CellProtection cp_unlocked.islocked = false sh=ThisComponent.Sheets.getByIndex(shindex) sh.unProtect(cPWD) With com.sun.star.sheet.CellFlags iWhat= .VALUE + .DATETIME + .STRING + .FORMULA End With oFilled = sh.queryContentCells(iWhat) oBlanks = sh.queryEmptyCells() rem isLocked is read only isLocked=true will not work. New object needs to be assigned to CellProtection oBlanks.CellProtection = cp_unlocked oFilled.CellProtection = cp_locked sh.protect(cPWD) End Sub
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
-
- Volunteer
- Posts: 1547
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: [Solved] Turn on/off cell protection using BASIC macro
Not entirely sure what command line you are referring to
If you want to gather more input from the user then create a new macro using Input() boxes to gather data that you can then pass to your original macro. In that case the new macro would be bound to the key press.
but you can use Tools->Customize->Keyboard to bind a macro to a particular key press.a way of calling such a macro from the command line?
If you want to gather more input from the user then create a new macro using Input() boxes to gather data that you can then pass to your original macro. In that case the new macro would be bound to the key press.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
I am trying to protect a spread sheet from the command line in a script
I am generating .xml spreadsheets on a Linux box in a crotab scrip . I need to prevent the customer from altering the spreadsheet. I want to protect either the document or individual sheets and then save the spreadsheet as .xls
The process producing these has to run 24/7 without user interaction.
I am generating .xml spreadsheets on a Linux box in a crotab scrip . I need to prevent the customer from altering the spreadsheet. I want to protect either the document or individual sheets and then save the spreadsheet as .xls
The process producing these has to run 24/7 without user interaction.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
Re: [Solved] Turn on/off cell protection using BASIC macro
Completely off topic. Not related to the office API. I guess ".xml spreadsheets" means that outdated XML flavour of Excel 2003? You are the developer of that script so it is your obligation to find out the right attributes for that Microsoft file format.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
Thanks for the help ☺☺☺ thought this was a friendly forum. It's not possible to protect xml , I don't know how to produce a .xls file from the system I have.Villeroy wrote:Completely off topic. Not related to the office API. I guess ".xml spreadsheets" means that outdated XML flavour of Excel 2003? You are the developer of that script so it is your obligation to find out the right attributes for that Microsoft file format.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
Re: [Solved] Turn on/off cell protection using BASIC macro
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
Anyone can revert that
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
Re: [Solved] Turn on/off cell protection using BASIC macro
Anyone can open an xml file and remove the protection.
Anyone can remove the sheet protection from an xls file.
Anyone can remove the sheet protection from an xls file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
Villeroy wrote:Anyone can open an xml file and remove the protection.
Anyone can remove the sheet protection from an xls file.
I am trying to learn how to use open office in batch mode , I have other things to do. This is just an example. If you can give me hints on how to do that I would be grateful.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
Re: [Solved] Turn on/off cell protection using BASIC macro
I am not a human macro recorder. What you've got to do is learning the mere basics about AOO macro programming.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 17
- Joined: Fri Jan 22, 2016 2:00 pm
Re: [Solved] Turn on/off cell protection using BASIC macro
fine don't help. Usually members of forums are willing to give advice. I hope that you are not typical of people on this forum. On forums concening topics where I have gained some knowledge in the 40+ years I have been in computing I freely offer adviceVilleroy wrote:I am not a human macro recorder. What you've got to do is learning the mere basics about AOO macro programming.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
Re: Turn on/off cell protection using BASIC macro
I got an error message:Charlie Young wrote:Hastily adapted and not carefully tested, but I'm sure what you want can be done. For the cells in oRange:
Code: Select all
Sub LockCells(oRange As Object) 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 = oRange.RangeAddress.Sheet oSheet.unprotect("Password") 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("Password") End Sub
From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?Message: wrong number of parameters!
Last edited by oonk on Sat Oct 19, 2019 7:45 am, edited 1 time in total.
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |
Re: [Solved] Turn on/off cell protection using BASIC macro
There are some blank cells locked.sm00th wrote:I've used slightly modified version of Villeroy's macro which works ok.
Thank you for posting this.
Code: Select all
Sub lock_sheets lock_nonempty_cells(1) lock_nonempty_cells(2) lock_nonempty_cells(3) End Sub Sub lock_nonempty_cells(shindex As Integer) Const cPWD = "" dim cp_locked as new com.sun.star.util.CellProtection cp_locked.islocked = true dim cp_unlocked as new com.sun.star.util.CellProtection cp_unlocked.islocked = false sh=ThisComponent.Sheets.getByIndex(shindex) sh.unProtect(cPWD) With com.sun.star.sheet.CellFlags iWhat= .VALUE + .DATETIME + .STRING + .FORMULA End With oFilled = sh.queryContentCells(iWhat) oBlanks = sh.queryEmptyCells() rem isLocked is read only isLocked=true will not work. New object needs to be assigned to CellProtection oBlanks.CellProtection = cp_unlocked oFilled.CellProtection = cp_locked sh.protect(cPWD) End Sub
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |
Re: [Solved] Turn on/off cell protection using BASIC macro
Are you sure if they are blank? Maybe those cells contain one or more whitespace characters or a nullstring (as a result of the formula). Such cell are not blank/empty really.There are some blank cells locked.
Can you upload an .ods type sample file here?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Turn on/off cell protection using BASIC macro
The passed parameter must be a range Object, but not a cell reference string.From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?
You must get the cellrange object first by its reference, and it must pass the object to the subroutine written by Charlie Young.
Finally you must to organise your code into a "for" loop: you must get and pass the desired ranges one-by-one to the subroutine.
Please upload an .ods type sample file here.
Last edited by Zizi64 on Sat Oct 19, 2019 10:43 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: [Solved] Turn on/off cell protection using BASIC macro
LibreOffice can do this by means of conditional formatting.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Turn on/off cell protection using BASIC macro
Thank you so much for your assistance, please see attached file.Zizi64 wrote:Are you sure if they are blank? Maybe those cells contain one or more whitespace characters or a nullstring (as a result of the formula). Such cell are not blank/empty really.There are some blank cells locked.
Can you upload an .ods type sample file here?
- Attachments
-
- LibreOfficeCalcSampleCodes_ProtectedCells_ForOOoForum.ods
- (40.73 KiB) Downloaded 302 times
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |
Re: [Solved] Turn on/off cell protection using BASIC macro
Please see code in the same attached file in this post >> viewtopic.php?f=20&t=34393&p=479786#p479786 .Zizi64 wrote:The passed parameter must be a range Object, but not a cell reference string.From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?
You must get the cellrange object first by its reference, and it must pass the object to the subroutine written by Charlie Young.
Finally you must to organise your code into a "for" loop: you must get and pass the desired ranges one-by-one to the subroutine.
Please upload an .ods type sample file here.
Last edited by oonk on Sun Oct 20, 2019 5:32 pm, edited 1 time in total.
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |