[Solved] Turn on/off cell protection using BASIC macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
wrrr
Posts: 9
Joined: Fri Sep 24, 2010 9:26 pm

[Solved] Turn on/off cell protection using BASIC macro

Post by wrrr »

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
Last edited by Hagar Delest on Fri Oct 01, 2010 11:53 am, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2.0 OOO320m12 (build 9483) on LinuxMint 9 (Ubuntu 10.04) i386
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Turn on/off cell protection using BASIC macro

Post by Villeroy »

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.
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
wrrr
Posts: 9
Joined: Fri Sep 24, 2010 9:26 pm

Re: Turn on/off cell protection using BASIC macro

Post by wrrr »

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.
Hello.
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
I want it to be invoked upon document closing.

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
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Turn on/off cell protection using BASIC macro

Post by Charlie Young »

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
wrrr
Posts: 9
Joined: Fri Sep 24, 2010 9:26 pm

Re: Turn on/off cell protection using BASIC macro

Post by wrrr »

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

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 =)
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
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Turn on/off cell protection using BASIC macro

Post by Charlie Young »

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)
Apache OpenOffice 4.1.1
Windows XP
wrrr
Posts: 9
Joined: Fri Sep 24, 2010 9:26 pm

Re: Turn on/off cell protection using BASIC macro

Post by wrrr »

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)
Hi,

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Turn on/off cell protection using BASIC macro

Post by Villeroy »

This makes use of the OpenOffice.org-API and works instantly with a cell style "Unlocked":

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
Same stuff in Python:

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
wrrr
Posts: 9
Joined: Fri Sep 24, 2010 9:26 pm

Re: Turn on/off cell protection using BASIC macro

Post by wrrr »

Villeroy wrote:This makes use of the OpenOffice.org-API and works instantly with a cell style "Unlocked":

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
Same stuff in Python:

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,
My God, you're genius =)
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

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.
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
sm00th
Posts: 1
Joined: Tue Jan 07, 2014 11:31 am

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by sm00th »

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 3.5 on Ubuntu 12.04
jsquareg
Posts: 18
Joined: Wed Dec 03, 2014 7:01 pm

Re: Turn on/off cell protection using BASIC macro

Post by jsquareg »

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

wrrr wrote:
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

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 =)
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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

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
can anyone point me to a way of calling such a macro from the command line?
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by UnklDonald418 »

Not entirely sure what command line you are referring to
a way of calling such a macro from the command line?
but you can use Tools->Customize->Keyboard to bind a macro to a particular key press.
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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

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.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

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.
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.
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

Anyone can revert that
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

Anyone can open an xml file and remove the protection.
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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

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
dfstrottersfan
Posts: 17
Joined: Fri Jan 22, 2016 2:00 pm

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by dfstrottersfan »

Villeroy wrote:I am not a human macro recorder. What you've got to do is learning the mere basics about AOO macro programming.
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 advice
LibreOffice Version: 5.0.4.2 on Windows 10 LibreOffice on Ubuntu and SUSE
oonk
Posts: 23
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

Re: Turn on/off cell protection using BASIC macro

Post by oonk »

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

I got an error message:
Message: wrong number of parameters!
From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?
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 |
oonk
Posts: 23
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by oonk »

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
There are some blank cells locked.
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Zizi64 »

There are some blank cells locked.
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.

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.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Zizi64 »

From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?
The passed parameter must be a range Object, but not a cell reference string.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by Villeroy »

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
oonk
Posts: 23
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by oonk »

Zizi64 wrote:
There are some blank cells locked.
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.

Can you upload an .ods type sample file here?
Thank you so much for your assistance, please see attached file.
Attachments
LibreOfficeCalcSampleCodes_ProtectedCells_ForOOoForum.ods
(40.73 KiB) Downloaded 299 times
| Fedora 31 Workstation Cinnamon of Fedora from Spins | LibreOffice 6.2.8.2-2 | Base with embedded Firebird |
oonk
Posts: 23
Joined: Fri Oct 04, 2019 8:03 am
Location: Pathum Thani, THAILAND

Re: [Solved] Turn on/off cell protection using BASIC macro

Post by oonk »

Zizi64 wrote:
From this code, how can I lock cell range A1:B4, A5:M9 and A6:M100 respectively ?
The passed parameter must be a range Object, but not a cell reference string.
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.
Please see code in the same attached file in this post >> viewtopic.php?f=20&t=34393&p=479786#p479786 .
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 |
Post Reply