Font colour in cell table

SupportCommunity Automation and VBA Font colour in cell table

Ansicht von 7 Beiträgen - 16 bis 22 (von insgesamt 22)
  • Autor
    Beiträge
  • #9322
    HerveM1234HerveM1234
    Teilnehmer

    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

    #9323
    HerveM1234HerveM1234
    Teilnehmer

    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

    #9324
    HerveM1234HerveM1234
    Teilnehmer

    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

    #9325
    Bernhard KantzBernhard Kantz
    Moderator

    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
    #9328
    Bernhard KantzBernhard Kantz
    Moderator

    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.

    #9329
    HerveM1234HerveM1234
    Teilnehmer

    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

    #9330
    Bernhard KantzBernhard Kantz
    Moderator

    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.

Ansicht von 7 Beiträgen - 16 bis 22 (von insgesamt 22)
  • Du musst angemeldet sein, um auf dieses Thema antworten zu können.