Font colour 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

Dim myValue As Double
myValue = Val(myString) 

' or

Dim myInt As Integer
myInt = CInt(myString)

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

Dim myLimit As Double
myLimit = 100

If myValue > myLimit Then
	MsgBox ("red")
Else
	MsgBox ("green")
End If

[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.

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

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