[Solved] Multiple =TEXT(Value("tom") or...

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
jvleuven
Posts: 3
Joined: Wed Jun 09, 2021 9:59 am

[Solved] Multiple =TEXT(Value("tom") or...

Post by jvleuven »

//////////////////////////////////////////////////////////////////////////////////////////////
SOLVED ANSWER PROVIDED BY A FORUM MEMBER AND ADJUSTMENTS I PERFORMED FOR IMPLEMENTATION
//////////////////////////////////////////////////////////////////////////////////////////////

The answer provided by: Matareuz was the correct methodology, so thank you very much!

I only made a small append of:

Code: Select all

REM deal with a cleared field, or error change to something else
Case Else
    oRange = CreateRange(oActiveSheet,RangueAddress.StartColumn+c,RangueAddress.StartRow+r)
    oRange.CellBackColor = RGB ( 255, 255, 255 )
and of course RGB colors, and naming convention for the modules. I didn't want to run it manually, and knew no one at this office would have the first idea how to, so had to do a bit of digging to find out if the Calc had listeners for the sheet itself. Apparently indeed it does!

At the bottom of the sheet to which I desired to assign the macro, I right-clicked the name.
this gave me a context menu (on Windows 10 Pro) with an option called, "Sheet Event" - which I selected.

A dialogue window named "assign action" rendered, with the option selection was "Content Changed"
which I selected, and then clicked "Macro..." under the "Assign:" in the top right of that dialogue.

Where i was able to select the Macro itself. This is a generic style change for color coding, so it isn't a requirement to bind it to the sheet, being aesthetic. so i saved it in my macros so we could simply assign it to each created sheet manually (for now) while I build the automation to avoid ever building these things again :D

I apologize for my late reply, and pre-pend of success on this post - this is a side project (what I'm doing with Open Office and LibreOffice) and my full time life is quite demanding. thank you to everyone for your answers :)

I truly hope this helps someone else.

//////////////////////////////////////////////////////////////////////////////////////////////
ORIGINAL QUESTION BELOW
//////////////////////////////////////////////////////////////////////////////////////////////

After reading as many forum posts as time is allotting, and reading the tutes and such, I still haven't found a solution that solves something which should be simple. In code I have a function to simply do a field test in a web form, but this is apparently not that simple heh

I want to handle multiple elements in a single entry, in every cell which is selected within the sheet to which the function should apply

if the field text is equal to Tom, Dick, or Harry irrelevant of the case or mixed case etc then change the background color to yellow
if the field text is equal to Jane, Mary, or Sue irrelevant of case or mixed case etc then change the background color to pink

if anything else is entered into the field, do nothing.

so

Code: Select all

if (TEXT(VALUE=["tom","mary","sue"]) use style "yellow") endif
if (TEXT(VALUE=["jane","mary","sue"]) use style "pink") endif
that is what I'm trying to accomplish, with an array, allowing me to add as many variations or arguments as I move forward into the array
Last edited by jvleuven on Mon Jun 14, 2021 4:03 am, edited 4 times in total.
OpenOffice 4.1.10 on Windows 10 Pro / LibreOffice 7.1.3.2 on MacOS Catalina 10.15.7
Matareuz
Posts: 23
Joined: Fri Nov 20, 2020 4:33 pm
Location: Venezuela

Re: Multiple =TEXT(Value("tom") or (value("dick") or...

Post by Matareuz »

Hey,

Welcome to the forum.

This code may can help you, it works in LibreOffice, but I'm not sure if it works in OpenOffice as well.

Code: Select all

Sub Main
Dim oActiveSheet, oSel, oRange As Object
Dim mArray(), mTmp()
Dim c, r As Integer
Dim RangueAddress As New com.sun.star.table.CellRangeAddress

oSel = ThisComponent.getCurrentSelection
oActiveSheet = ThisComponent.getCurrentController.getActiveSheet
mArray = oSel.getDataArray
RangueAddress = oSel.getRangeAddress

	For r = 0 to Ubound(mArray)
		mTmp = mArray(r)
	
		For c = 0 to Ubound(mTmp)
	
			Select Case UCase(mArray(r)(c)) 
			Case "TOM", "DICK", "HARRY"
			oRange = CreateRange(oActiveSheet,RangueAddress.StartColumn+c,RangueAddress.StartRow+r)
			oRange.CellBackColor = RGB ( 255, 255, 0 )
			Case "JANE", "MARY", "SUE" 
			oRange = CreateRange(oActiveSheet,RangueAddress.StartColumn+c,RangueAddress.StartRow+r)
			oRange.CellBackColor = RGB ( 219, 26, 187 )
			End Select
		
		Next
	
	Next

End Sub

Function CreateRange (oActiveSheet As Object,Column As Integer,Row As Integer) As Object
CreateRange = oActiveSheet.getCellRangeByPosition(Column,Row,Column,Row)
End Function
LibreOffice 6.3.2.2 Windows 7 Ultimate
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Multiple =TEXT(Value("tom") or (value("dick") or...

Post by Villeroy »

=STYLE(IF(MATCH(A1;{"Tom";"Dick";"Harry";"Jane";"Mary";"Sue"};0)<4;"yellow";"pink"))

The TEXT function works like format(num, format-code) in normal programming langauges.
VALUE works like double(expr) in normal languages. VALUE("Jane") never gives anything numeric.
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
jvleuven
Posts: 3
Joined: Wed Jun 09, 2021 9:59 am

Re: Multiple =TEXT(Value("tom") or (value("dick") or...

Post by jvleuven »

Thank you for your answer. So you know - based upon what you had written it works as well in OpenOffice - however it isn't the solution I'm looking for unfortunately. I realized after I ran it, that I had to actually run it haha

I'm looking for an event listener approach. The scenario is as follows: I'm throwing together a simple system for a friend, for the SRO (single room occupancy) residence, where there are 5 floors, and 30 units per floor. These are single rooms, with shared restroom facilities, for the "marginalized" within the geographical area. Low rent. Therefore as to be expected, low pay (everyone who works there is on minimum wage, and most were themselves formerly marginalized in some capacity).

I simply wanted an event listener, onBlur() (simple typeScript or Javascript - though I was fine in vb, py, c, etc as long as it listens) With the existent header in the calc sheets, I'm dealing with a sheet of 141 rows, and at most 31 columns - for the days of the month - of course by 12 sheets. 95% of the entries per field will start with "S" so a simple listener for the entire page of:

Code: Select all

if(text(value) == 's') end if
else ( argument to test for array1 or array2 content onBlur)
end if

(pseudo code)
I'm beginning to realize that approach isn't really an "office" application methodology, though to be honest, I find it odd that (from what I can gather thus far) automation of simple tasks isn't really how either of these applications function (though I could be incorrect, having not dived into this full time). I do say I liked your solution, as a holistic over all methodology, for a range selection, because I couldn't imagine having to add a formula to every single field as a test haha - because to me that screams universal function, write once, use many - anywhere, anytime.

I'll keep digging to see if there are any methods where I can write my own automated modules or extensions or what have you to apply a universal approach for those less technically inclined


Matareuz wrote:Hey,

Welcome to the forum.

This code may can help you, it works in LibreOffice, but I'm not sure if it works in OpenOffice as well.

Code: Select all

Sub Main
Dim oActiveSheet, oSel, oRange As Object
Dim mArray(), mTmp()
Dim c, r As Integer
Dim RangueAddress As New com.sun.star.table.CellRangeAddress

oSel = ThisComponent.getCurrentSelection
oActiveSheet = ThisComponent.getCurrentController.getActiveSheet
mArray = oSel.getDataArray
RangueAddress = oSel.getRangeAddress

	For r = 0 to Ubound(mArray)
		mTmp = mArray(r)
	
		For c = 0 to Ubound(mTmp)
	
			Select Case UCase(mArray(r)(c)) 
			Case "TOM", "DICK", "HARRY"
			oRange = CreateRange(oActiveSheet,RangueAddress.StartColumn+c,RangueAddress.StartRow+r)
			oRange.CellBackColor = RGB ( 255, 255, 0 )
			Case "JANE", "MARY", "SUE" 
			oRange = CreateRange(oActiveSheet,RangueAddress.StartColumn+c,RangueAddress.StartRow+r)
			oRange.CellBackColor = RGB ( 219, 26, 187 )
			End Select
		
		Next
	
	Next

End Sub

Function CreateRange (oActiveSheet As Object,Column As Integer,Row As Integer) As Object
CreateRange = oActiveSheet.getCellRangeByPosition(Column,Row,Column,Row)
End Function
OpenOffice 4.1.10 on Windows 10 Pro / LibreOffice 7.1.3.2 on MacOS Catalina 10.15.7
jvleuven
Posts: 3
Joined: Wed Jun 09, 2021 9:59 am

Re: Multiple =TEXT(Value("tom") or (value("dick") or...

Post by jvleuven »

I wanted to thank you for your answer, though a single cell approach is inefficient for my uses, when in fact I simply need an onBlur() event listener. I have responded more in depth here -> viewtopic.php?f=9&t=105439&p=511877#p511935 thank you
Villeroy wrote:=STYLE(IF(MATCH(A1;{"Tom";"Dick";"Harry";"Jane";"Mary";"Sue"};0)<4;"yellow";"pink"))

The TEXT function works like format(num, format-code) in normal programming langauges.
VALUE works like double(expr) in normal languages. VALUE("Jane") never gives anything numeric.
OpenOffice 4.1.10 on Windows 10 Pro / LibreOffice 7.1.3.2 on MacOS Catalina 10.15.7
Post Reply