[Solved] Adding Conditional Formats using Basic Macro

[markovian]

Hi,

I have a Calc Spreadsheet where I want to update/add conditional formats using the Basic API.

I already have a Basic Macro that monitors cells for changes (i.e. by adding Listeners on document opening). This is working fine. Now I want to: 1) Update ConditionalFormat(0)'s Formula1 and StyleName and 2) Create an additional Conditional Format. The script below runs without errors, but nothing gets updated/added. I can't figure out how to update the array. Any help appreciated.

Code: Select all

Sub CellListener_Modified( oEvent as Object )
	' Update Conditional Format's Formula1 & StyleName
	oActiveSelection = ThisComponent.CurrentController.Selection
	aConditionalFormat0 = oActiveSelection.ConditionalFormat(0)
	aConditionalFormat0.setFormula1("B15 = """"")
	aConditionalFormat0.setStyleName("Result2")
	oActiveSelection.ConditionalFormat(0) = aConditionalFormat0

	' Add Additional Conditional Format
	aNewConditionalFormat = aConditionalFormat0
	aNewConditionalFormat.setFormula1("B15 <> """"")
	aNewConditionalFormat.setStyleName("Heading")
	oActiveSelection.setPropertyValue("ConditionalFormat", aNewConditionalFormat)
End Sub

[rudolfo]

Hm, I am just experimenting a bit with the MRI extension on cells with conditional formats. Could it be that you also have to use .setOperator(com.sun.star.sheet.ConditionOperator.FORMULA)?
And according to the API a conditional format is a UNO service not a UNO struct. So I would say it needs a aNewConditionalFormat = CreateUnoService("com.sun.star.sheet.TableConditionalEntry") when creating a new conditional format.

I am only guessing here ... as I have not much experience with conditional formats ...

[markovian]

Thanks for the ideas rudolfo.

After much experimentation, I solved the problem and am now able to successfully create conditional formats using a Basic Macro. The reason I wanted to do this was to try and get around the limit of only 3 Conditional Formats using the gui. I really wish this limitation could be removed. I actually downloaded the source code for LibreOffice, compiled, and tried finding where the gui code is kept. Seems beyond my level of programming at this point.

I put together an example that shows how to use the macro to create conditional formatting on the current selection in a spreadsheet. I thought others could benefit from using it / or learning from it. In order to use it, update the conditions on the Conditions Sheet, then select the cells you want to format on the Results Sheet (doesn't have to be this sheet, any active sheet will work) and run the macro createConditionalFormatting. Below is the code:

Code: Select all

Sub createConditionalFormatting
	' Clear Existing Selection Conditions
	Dim oSelectionRange as Object, oSelectionConditions as Object
	oSelectionRange = ThisComponent.CurrentSelection								' Store Selection Range
	oSelectionConditions = oSelectionRange.ConditionalFormat						' Store Selection Conditions
	oSelectionConditions.Clear()													' Clear Selection Conditions
	' Update Conditions Range
	Dim oConditionsRange as Object, oConditionsSheet as Object, oConditions as Object
	oConditionsSheet = ThisComponent.Sheets.getByName("Conditions")									' Store Conditions Sheet
	oCursor = oConditionsSheet.createCursorByRange(oConditionsSheet.GetCellbyPosition(0, 2))		' Create Cursor at "A3"
	oCursor.GotoEndOfUsedArea(True)																	' Curse Select Range to Last Used Cell
	oConditionsRange = oCursor																		' Store Conditions Range
	' Create New Conditions using Property Value
	Dim oNewCondition(4) as new com.sun.star.beans.PropertyValue									' Create New Property Value
	For i = 0 to oConditionsRange.Rows.Count - 1
		If oConditionsRange.getCellByPosition(0, i).String = "Absolute" Then
			oNewCondition(0).Name = "SourcePosition"
			oNewCondition(0).Value = oSelectionRange.ReferencePosition										' Use Selection Range's Reference Position for New Conditions
		End If							
		oNewCondition(1).Name = "Operator"
		oNewCondition(1).Value = getOperator(oConditionsRange.getCellByPosition(1, i).String	' Set Operator
		oNewCondition(2).Name = "Formula1"
		oNewCondition(2).Value = oConditionsRange.getCellByPosition(2, i).String				' Set Formula1 (Relative to Source Position if set, otherwise Relative to Current Cell)
		oNewCondition(3).Name = "Formula2"
		oNewCondition(3).Value = oConditionsRange.getCellByPosition(3, i).String				' Set Formula1 (Relative to Source Position if set, otherwise Relative to Current Cell)
		oNewCondition(4).Name = "StyleName"
		oNewCondition(4).Value = oConditionsRange.getCellByPosition(4, i).String				' Set StyleName
		oSelectionConditions.addNew(oNewCondition())														' Add New Conditional Format to Array
	Next i
	oSelectionRange.setPropertyValue("ConditionalFormat", oSelectionConditions)				' Add New Conditional Formats to Named Range's Referred Cells
End Sub

Function getOperator( sOperator as String )
	Select Case UCase(sOperator)
		Case ""
			getOperator = com.sun.star.sheet.ConditionOperator.NONE
		Case "="
			getOperator = com.sun.star.sheet.ConditionOperator.EQUAL
		Case "<>"
			getOperator = com.sun.star.sheet.ConditionOperator.NOT_EQUAL
		Case ">"
			getOperator = com.sun.star.sheet.ConditionOperator.GREATER
		Case ">="
			getOperator = com.sun.star.sheet.ConditionOperator.GREATER_EQUAL
		Case "<"
			getOperator = com.sun.star.sheet.ConditionOperator.LESS
		Case "<="
			getOperator = com.sun.star.sheet.ConditionOperator.LESS_EQUAL
		Case "BETWEEN"
			getOperator = com.sun.star.sheet.ConditionOperator.BETWEEN
		Case "NOT BETWEEN"
			getOperator = com.sun.star.sheet.ConditionOperator.NOT_BETWEEN
		Case "FORMULA"
			getOperator = com.sun.star.sheet.ConditionOperator.FORMULA
	End Select
End Function

The macro works very well except for when you put in compound conditions (i.e. AND(test1=true, test2=true). I think this may be a bug and will be posting another question and example that demonstrates the problem.

[dech]

Hi guys, the macro is awaysome!

But...when I try to use "Absolute" addressing an error shows: "Property or method was not found: ReferencePosition".
And I dont know what to do to make it work.

Im not programmer, im not familiar with intuitive programmig - i just copy and adjust macros but I dont know what to do when it comes on bigger changes.

[Villeroy]

Im not programmer, im not familiar with intuitive programmig - i just copy and adjust macros but I dont know what to do when it comes on bigger changes.

So you can not write nor adjust any macros. You do not even understand how a macro works nor how it fails.

Hi guys, the macro is awaysome!

Only if you have no clue of how to use an office suite. I don't see any use case why anyone able to use a spreadsheet productively should hard-code conditional formattings in silly Basic code.

[dech]

man...im not so terrible! I wrote that I can adjust.....in fact im proud of what I have done in 3 years with zero programming knowledge...spredaseeht that generates invoices,delivery notes,does plannin of goods, gpraphs....anyway...
I have spent hours to prepare sample of my problem - see attachment (maybe lot of reading...but the problems are very tricky).
The problem with this macro is somewhere else than I described before. (Actually I need relative addressing. Lets leave my previous question on absolute addressing)

I have 2 problems:
1)
the conditional formula doesnt calculate until I bring the GUI of conditional formatting of that cell and pres OK - now the cell changes the format.
I tried some variations(but are no use for my case) of formula - with AND, SUM - all these worked instantly. But my formula that I need to work(SUMPRODUCT), doesnt work - see attachment.

2)
I have another problem with construction of formula because this macro uses the formula AS IF IT WAS in cell A1. And that is the problem - I need to construct formula that involve cells left to column "A" and cells above row "1". The formatted area starts in e.g. B2 and I need in B2 to be applied conditional formula like this: SUM(A1:B2)
See attachment

thanks for reading till end and possibly helping me

[Villeroy]

Your C.F. of cell F11:

Code: Select all

IF($E11="customer";SUMPRODUCT(($E$11:$E$16="customer")*($D$11:$D$16=$D11)*F$11:$H$16)>SUMPRODUCT(($E$11:$E$16="supplier")*($D$11:$D$16=$D11)*F$11:$H$16);0)

Put that formula with a leading "=" into some helper cell, copy down and tweak it until gives the desired boolean results.
Having the helper formulas in X11:X16, your format condition for row 11 can be as simple as $X11

[dech]

you are trying to solve the problem "2)" right?
I know what you mean...but:
I cant use this formula...and copying down will not solve this... I need to use formula in J3....If I used the formula in F11 it would be applied on the cells F11 : J16 but the formulas in the cells will refer somwehere else - down where is nothing.
...eh...it is much easier to talk about it if you look to sample in attachment - problem 2) :
formula that I need in H37 is SUM(G36:H37)>0 ...in J37 i need SUM(I36:J37) (you cant use $ to achieve this) , it means that the formula which would be put in the macro would look like this: SUM(-A-1:A1)>0 of course it doesnt work because of the "negative" coordinates. I think that there are two ways how to achieve it: A) work around somehow the negative coordinates B) teach the macro to not use A1 as a default cell from which are the formats "copied"(you need to have the formula in a state AS iF IT WAS IN A1 cell)
(all this is written in the attachment)

anyway - forget about F11:G16 - I just put it there to see that the conditional formatting works there instantly, but from I11:J16 not(you need to pop up the conditional formatting GUI,hit OK and then it works).

[Villeroy]

The OFFSET calculates any rectangular cell range you need.
I don't understand that sheet so I don't understand why the c.f. in I:J does not refresh. May be you found some bug or something?

All I know for sure is that macros are evil, inefficient, incompatible, particularly those written in an extinct lingo called "Basic".
And it seems to me that you try to do very simple things in the most complicated ways.

[dech]

it seems that you are right in the last sentence at all
I have solution that works for both problems - principle (forget about my attachment):
Im not going to use this macro for this complicated case (for some others yes), instead, Im going to prepare somewhere "source cell" in which I have working conditional formula (I have already poped up the conditional GUI and hit OK). This source cell is located let say in H10(not A1 for sure) - so now I can easily edit the formula and involve in it also rows that are above this source cell and columns that are to the left of this source cell.
Now I just need to copy this cell and paste it in area - but only the FORMAT (not numbers,text,date....): Edit - paste special (im not sure about translation of the menu items-i have OO not in english). And that is IT!