Hi fellows and thanks for your attention. First, I learned VBA
with Excel by my self. I have no expertise programming in any
language. For now on, I have to use Calc as much as Excel.
I'm trying to include conditional format thru a basic program.
I have to create a spreadsheet with conditional format with lots of
cells. Every cell will have a unique formula.
Everything I found so far, led me to code below. This is a very short
example, not the real one, for better view.
The extrange thing
is: the cell reference in the formula gets totally changed.
To understand what I'm writing about, copy this code and execute it.
Don't forget to change sheet name in code or
just rename your sheet with "Example".
After, go to cell B7 and look at conditional format using format menu.
You will see B7 changed to C13. And at B8,
you will see B7 changed to C15. And you may see any other cell inserted
by this code has the same problem (B7->C13,
C7->D13,B8->C15,C8->D15,...).
I used it at version 3.3.3. Also downloaded version 4.0.3 and the
problem still remain.
Also tried use absolute reference. The
formula keeps right but not work in sheet. I have to remove "$"
manually. When it done, works fine.
Somebody know what's happening?
I post this queston at AskLibo in August 7 and nothing so far.
For any help, many thanks...
Sub Example
Dim mCond(2) As New com.sun.star.beans.PropertyValue
Dim oEntrys As Variant
Dim oCell as object, oDoc as object, oSheet as object
Dim iLine as integer, iColumn as integer
Dim aColumn(9) as string
oDoc = ThisComponent
oSheet=oDoc.Sheets.getByName("Example")
aColumn(0)="A": aColumn(1)="B": aColumn(2)="C": aColumn(3)="D"
aColumn(4)="E": aColumn(5)="F": aColumn(6)="G": aColumn(7)="H"
aColumn(8)="I": aColumn(9)="J"
For iLine = 6 to 9
For iColumn = 1 to 9
oCell = oSheet.getCellByPosition(iColumn,iLine)
oEntrys = oCell.getPropertyValue("ConditionalFormat")
oEntrys.clear()
if iLine mod 2 = 0 then
' this formula start at line 7, first cell B7
' looking at sheet, format menu, conditional format you will see C13
mCond(0).Name = "Operator"
mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & iLine+1 & ">80"
mCond(2).Name = "StyleName"
mCond(2).Value = "Good"
oEntrys.addNew(mCond())
oCell.setPropertyValue("ConditionalFormat", oEntrys)
mCond(0).Name = "Operator"
mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & iLine+1 & ">60"
mCond(2).Name = "StyleName"
mCond(2).Value = "Average"
oEntrys.addNew(mCond())
oCell.setPropertyValue("ConditionalFormat", oEntrys)
else
' this start at line 8, first cell B8, and in this case you see C15
mCond(0).Name = "Operator"
mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & iLine+1 & ">80"
mCond(2).Name = "StyleName"
mCond(2).Value = "Keep"
oEntrys.addNew(mCond())
oCell.setPropertyValue("ConditionalFormat", oEntrys)
mCond(0).Name = "Operator"
mCond(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
mCond(1).Name = "Formula1"
mCond(1).Value = aColumn(iColumn) & iLine+1 & "<60"
mCond(2).Name = "StyleName"
mCond(2).Value = "Review"
oEntrys.addNew(mCond())
oCell.setPropertyValue("ConditionalFormat", oEntrys)
end if
Next
Next
End Sub
José Ramos
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.