Font color in cell table

[HerveM1234]

Hi,

Thanks for your reply.
The font setting works very well

The problem I have is the value : myCell.Text gives something like :%{myfpscript_expression}
How in VBA can I compare this “value” to an integer or an string for example ?

Thanks again

[HerveM1234]

Hi,

Thanks for your reply.
The font setting works very well

The problem I have is the value : myCell.Text gives something like :%{myfpscript_expression}
How in VBA can I compare this “value” to an integer or an string for example ?

Thanks again

[HerveM1234]

Hi,

Thanks for your reply.
The font setting works very well

The problem I have is the value : myCell.Text gives something like :%{myfpscript_expression}
How in VBA can I compare this “value” to an integer or an string for example ?

Thanks again

[Bernhard Kantz]

You should loop through your table and convert the string extracted from each cell to a value.
You can use for example

[code]Dim myValue As Double
myValue = Val(myString)

‘ or

Dim myInt As Integer
myInt = CInt(myString)[/code]

to do this. Then you’re able to check, if the value is within your limits:

[code]Dim myLimit As Double
myLimit = 100

If myValue > myLimit Then
MsgBox (“red”)
Else
MsgBox (“green”)
End If[/code]

[Bernhard Kantz]

To evaluate the string expression contained in the .Text property of the CellTableCell object one may use the FPScript function Execute(). In the following macro this formula is generated on the fly and deleted after execution.

[code]
Option Explicit

Public Sub ColorizeCells()
Dim myTable As CellTable
Set myTable = ActiveDatabase.ActiveFolder.Object(“Zellentabelle”, _
fpObjectTypeCellTable)

Dim oEvaluator As Formula
Set oEvaluator = ActiveDatabase.ActiveFolder.Add(“Eval”, fpObjectTypeFormula, _
fpNameClashHandlingAutoRename)
oEvaluator.Formula = “Arguments str” & vbCrLf & “Execute(str)”

Dim nRows As Integer, nCols As Integer
nRows = myTable.Cells.NumberOfRows
nCols = myTable.Cells.NumberOfColumns

Dim i As Integer
Dim res As Double
For i = 1 To 3
‘ cells are numbered row-wise so 2 + i * nCols enumerates the cells
‘ from the second column starting at the second row
With myTable.Cells(2 + i * nCols).Text
res = oEvaluator.Call(ExtractExpression(.Text))
If res < -1 Then
.Font.Color = vbBlue
ElseIf res <= 1 Then
.Font.Color = vbBlack
Else
.Font.Color = vbRed
End If
End With
Next i

oEvaluator.Delete
End Sub

Private Function ExtractExpression(str As String)
str = Mid$(str, InStr(str, "{") + 1)
ExtractExpression = Left$(str, InStrRev(str, "}") – 1)
End Function
[/code]

This macro assumes a cell table named "Zellentabelle" with atleast two columns and four rows. It scans the 3 cells in the second column starting from the second row and changes the font color to blue for values less than -1, red for values greater than +1, values between this limits are colored black.

[HerveM1234]

Hi,

Thanks again for that, but :
– the Execute FPS function doesn’t work with data series !
– 100 lines of code just to change the cell colours !

I will try to export the tables to an Excel File and work with the built-in functions of Excel to modify the cells colours.

Please could you :
– Post a VBA example of FlexPro Table exportation to Excel Worsheet
– Upgrade the current version of FlexPro with this functionnality

rv

Attached : an example of Execute function

[Bernhard Kantz]

The automatic coloring of table cells depending on their value is indeed planned for FlexPro 10.
To transfer a FlexPro table to Excel, copy and paste via the ordinary clipboard mechanism can be used: Select the whole table (using Ctrl-A) and use Ctrl-C followed by Ctrl-V in the destination Excel table to insert the content.

[Author]

Posts