Ask Your Question
0

Conditional Formatting within a Calc Macro [closed]

asked 2012-10-25 09:20:28 +0100

Gaffer gravatar image

This is a short BASIC Calc macro intended to build a simple spreadsheet with multiple "conditional formatting" cells. It runs cleanly but does not create the conditional formats. Clearly I am misunderstanding something important. A pointer would be appreciated.

Sub Main
    oDoc = ThisComponent
    oDoc.sheets(0).getCellByPosition(0, 0).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 0).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 0).ConditionalFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "A1=5" ), _
        MakePropertyValue( "StyleName", "Heading" ) ) 
    oDoc.sheets(0).getCellByPosition(0, 1).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 1).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 1).ConditionalFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ), _
        MakePropertyValue( "Formula1", "A2=5" ), _
        MakePropertyValue( "StyleName", "Result" ) ) 
End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-18 18:56:23.074745

1 Answer

Sort by » oldest newest most voted
0

answered 2012-10-25 10:28:42 +0100

JohnSUN gravatar image

updated 2012-10-25 10:30:17 +0100

Try to save the object ConditionalFormat for this cell in a separate variable. Change its content using Clear and AddNew (or getByIndex and setters). And give back to the property ConditionalFormat for this cell with setPropertyValue("ConditionalFormat",...)

...
    oldCondFormat = oDoc.sheets(0).ConditionalFormat
    oldCondFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "A1=5" ), _
        MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, 0).getCellAddress() ), _
        MakePropertyValue( "StyleName", "Heading" ))) 
    oDoc.sheets(0).getCellByPosition(1, 0).setPropertyValue("ConditionalFormat", oldCondFormat)
...
edit flag offensive delete link more

Comments

Excellent. Thankyou.

Gaffer gravatar imageGaffer ( 2012-10-25 11:34:11 +0100 )edit

This is a great answer but i don;t understand why in the first line of your example code you chose oDoc.sheets(0).ConditionalFormat, which looks to me like a conditional format associated with the whole sheet rather than something like oDoc.sheets(0).getCellByPosition(1,0).ConditionalFormat ?

Gaffer gravatar imageGaffer ( 2012-10-25 15:04:43 +0100 )edit

Oh, sorry! It's just an unfortunate typo. Of course, you are absolutely right: for the conditional format the entire sheet should be used for reading and appropriation of this sheet, and for individual cells - their own conditional format. Their structure and methods are the same at now...

JohnSUN gravatar imageJohnSUN ( 2012-10-25 15:17:51 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2012-10-25 09:20:28 +0100

Seen: 3,186 times

Last updated: Oct 25 '12