Change Individual Table Cell Text Color in DIAdem Report

This article demonstrates how to programmatically change the color of the text in individual table cells in a DIAdem Report.

Changing the cell text color requires the use of a DIAdem User Command. The User Command defines a Sub Procedure that changes the font color of a cell depending on the cell's value. This Sub Procedure is then assigned to an entire table column and will execute when the cell is plotted.

Follow the steps below to programmatically change the color of the text for individual cells of a 2D table in a DIAdem Report.

1. In DIAdem, select the SCRIPT panel and click File >> New VBS to create a new script. This script will be a new User Command.
2. In the script, create a new Sub Procedure called MyOnDrawingCell that takes a Content and Cell parameter.
   • This Sub Procedure will be called when a table cell is plotted. Therefore, the Report table will automatically pass in the cell data and table content.

Sub MyOnDrawingCell(Content, Cell)

End Sub

3. Inside the Sub Procedure, define variables dValue and dRow to store the active cell's value and row index respectively.
4. Set dValue as val(Cell.value) and dRow as Content.Row

Dim dValue, dRow

dValue = val(Cell.Value)
dRow = Content.Row

5. Assuming that the table columns will have Headers, place an If statement to check if the current row index > 1.
   • This is necessary to ensure that the color of the Header text is not changed.

If dRow > 1 Then

End If

6. Inside the If statement, place another If statement to check if the current cell's value is over a certain threshold.
   • The code snippet below checks if the cell's value is >70 or <20.

If dValue > 70 Then
      '-- do something
ElseIf dValue < 20 Then
      '-- do something else
Else
      '-- do something else
End If

7. For each If condition, enter Cell.Font.Color.SetRGBColor(RGB(<x>,<y>,<z>)) where <x>, <y> and <z> represent the red, green and blue color components for the cell's text.
   • The below code snippet changes the cell text to red if dValue > 70, blue if dValue < 20 and black other wise.

Sub MyOnDrawingCell(Content, Cell)

  Dim dValue, dRow
  
  dValue = val(Cell.Value)
  dRow = Content.Row
  
  If dRow > 1 Then
  
    If dValue > 70 Then
      Cell.Font.Color.SetRGBColor(RGB(255,0,0))
    ElseIf dValue < 20 Then
       Cell.Font.Color.SetRGBColor(RGB(0,0,255))
    Else
       Cell.Font.Color.SetRGBColor(RGB(0,0,0))
    End If
  
  End If

End Sub

8. Save the script as MyUserCommand.VBS
9. Create a new script by clicking File >> New VBS.
10. Save the new script as Set Table Cell Text Color.VBS in the same folder as previous.
11. At the start of the script, register the User Command, as shown below.
    • This ensures that the MyDrawingCell Sub Procedure is accessible from DIAdem Report objects.

'-- Register User Command Script
Call ScriptCmdAdd(CurrentScriptPath & "MyUserCommand.VBS")

12. Load your data file into the Data Portal.
    • For demonstration purposes, this script loads a TDMS file from the script's parent folder.

'--- Load data file
Call Data.Root.Clear()
Call DataFileLoad(CurrentScriptPath & "Example File.tdms", "TDMS", "Load|ChnXYRelation")

13. Load a DIAdem Report file (.TDR) that contains a 2D Table.
    • The code snippet below loads a Report called Template Report.TDR from the script's parent folder. This Report contains an empty 2D Table.

'--- Load template report containing table
Call Report.LoadLayout(CurrentScriptPath & "Template Report.TDR")

14. Populate the 2D table with data from the Data Portal.
    • For demonstration purposes, the example TDMS file contains 3 channels containing temperature data. Each channel in plotted in a different column of the Table.
    • The code snippet below uses a For Loop to iterate through the 3 channels. For each channel, a new table column is created and the data is plotted.

'--- Iterate through Data Portal channels, and plot each one in a new column on the table
Dim i, oMy2DTable, oNewColumn
Set oMy2DTable = Report.ActiveSheet.Objects(1)
  
For i = 1 to Data.Root.ChannelGroups(1).Channels.Count
  Set oNewColumn = oMy2DTable.Columns.Add(e2DTableColumnChannel)
  oNewColumn.Channel.Reference = "[1]/[" & i & "]"
Next

15. For each channel whose cell text color you wish to change, set the User Command as the function to execute on the OnDrawingCell event.
    • The OnDrawingCell event is triggered when the table cell is plotted. By assigning this to the User Command created earlier, the current table cell's data is passed to the Sub Routine and the text color is changed accordingly.
    • The code snippet below iterates through each of the columns in the table and sets the OnDrawingCell event.

'--- Set the OnDrawingCellEvent to execute a User Command
For i = 1 to oMy2DTable.Columns.Count
  oMy2DTable.Columns.Item(i).Settings.OnDrawingCell = "MyOnDrawingCell"
Next

16. Refresh the Report to view updates.

Report.Refresh()

17. Save changes and execute the Set Table Cell Text Color.VBS script.
18. After executing, the 2D table on the Report should appear similar to below. All values above 70 are red, while all values below 20 are blue.
    • Note: If the table is not updating, you may need to close and re-open DIAdem. 

The files used throughout this example can be found attached to this article.