Ask Your Question

Conditional Formatting within a Calc Macro [closed]

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

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", ),_
        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", ), _
        MakePropertyValue( "Formula1", "A2=5" ), _
        MakePropertyValue( "StyleName", "Result" ) ) 
End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As
   Dim oPropertyValue As New
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   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

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

JohnSUN gravatar image

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

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", ),_
        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


Excellent. Thankyou.

Gaffer gravatar imageGaffer ( 2012-10-25 11:34:11 +0200 )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 +0200 )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 +0200 )edit

Question Tools

1 follower


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

Seen: 3,222 times

Last updated: Oct 25 '12