Table Cell Coloring help needed

[Pesmith8]

Hello Group,

I am working to make a 2dTable to be able to change font color based on exceeding limits values.

Background:

Am using DIAdem 2019 SP1, Windows 10 pro 64.

Was working with example from forum by Winner,   This works partly, in that it will color the first column but not the subsequent columns.  (in my case the 2Dtable is 7 columns wide!)

Code used for user Commands.

Sub MyOnDrawingCell_1(Context, Cell)
'If (Context.Table.Name = "2DTable") Then
If (Cell.Value > 0.0) Then
Cell.Font.Color.SetPredefinedColor(2)
else
Cell.Font.Color.SetPredefinedColor(5)

End If
'End If
End Sub

Sub MyOnDrawingCell_2(Context, Cell)

'If (Context.Table.Name = "2DTable") Then
If (Cell.Value > 0.0) Then
Cell.Font.Color.SetPredefinedColor(2)
else
Cell.Font.Color.SetPredefinedColor(5)
End If
'End If
End Sub

Code used to build the Table

'-------------------------------------------------------------------------------
'-- VBS script file
'-- Created on 04/15/2020 08:57:50
'-- Author:
'-- Comment:
'-------------------------------------------------------------------------------
Option Explicit 'Forces the explicit declaration of all the variables in a script.
Dim oMy2DTable, oMyPosition, oMyColumn1, oMyColumn2
Call Report.NewLayout()
Set oMy2DTable = Report.ActiveSheet.Objects.Add(eReportObject2DTable,"My2DTable")
Set oMyPosition = oMy2DTable.Position.ByBorder
oMyPosition.Top = 30
oMyPosition.Bottom = 20
oMyPosition.Left = 20
oMyPosition.Right = 30
Set oMyColumn1 = oMy2DTable.Columns.Add(e2DTableColumnChannel)
oMyColumn1.Channel.Reference = "[1]/[1]"
Set oMyColumn2 = oMy2DTable.Columns.Add(e2DTableColumnChannel)
oMyColumn2.Channel.Reference = "[1]/[2]"
oMy2DTable.Settings.IndexSettings.IndexMode = e2DTableIndexModeAutomaticallyIncreasing
oMyColumn1.Settings.OnDrawingCell = "MyOnDrawingCell_1"
oMyColumn2.Settings.OnDrawingCell = "MyOnDrawingCell_2"

Questions List:

1) When I run the script to make the table, the result is that I can get column 1 to correctly color the fonts. But it will not in Column 2. Do not understand why it is not working.

2) When I tried to set the event code from the GUI

Unsure what to put into this block.  Seems that if script set this value, then it works partially, but if set in gui have yet to get it to work.

If anyone can help with a working table building code or TDR and usercommand function that works, that would be very appreciated.

Paul

[Pesmith8 · Accepted solution]

Hi Group, and whoever wants to colorize table cells based on limits.

Was able to get this to work, with some help from friends (Thanks Julia!)

This user Command will work, and also needed to link the user command to run to the Table in VBS code.

Found that in using globals to transfer the min /max limits from code into the user command routine. In code was using if statements to determine which color to use. Found that had to set the type to integer explicitly after a multiply by 1000. The ompare would not work at all if did not do the type conversion. 

Also found that putting a check for required globals in the user command was a good idea. That way the global will always be present no matter what.

The Command to link the table column to the user command.

oMyColumn1.Settings.OnDrawingCell = "Colorize_CW_tables"
oMyColumn2.Settings.OnDrawingCell = "Colorize_CW_tables"
oMyColumn3.Settings.OnDrawingCell = "Colorize_CW_tables"
oMyColumn4.Settings.OnDrawingCell = "Colorize_CW_tables"

The user Command

Sub Colorize_CW_tables(Context, Cell)
' Makes sure always have these globals so that the values later will have values to work with
If Not Iteminfoget("gCol_1_max") Then
Call GlobalDim("gCol_1_max, gCol_1_min")
Call GlobalDim("gCol_2_max, gCol_2_min")
Call GlobalDim("gCol_3_max, gCol_3_min")
Call GlobalDim("gCol_4_max, gCol_4_min")
Call GlobalDim("gCol_5_max, gCol_5_min")
Call GlobalDim("gCol_6_max, gCol_6_min")
Call GlobalDim("gCol_7_max, gCol_7_min")
Call GlobalDim("gCol_8_max, gCol_8_min")
' these are default values that will be overwriten later
gCol_1_max = 1000
gCol_1_min = -1000

gCol_2_max = 1000
gCol_2_min = -1000

gCol_3_max = 1000
gCol_3_min = -1000

gCol_4_max = 1000
gCol_4_min = -1000

gCol_5_max = 1000
gCol_5_min =-1000

gCol_6_max = 1000
gCol_6_min = -1000

gCol_7_max = 1000
gCol_7_min = -1000

gCol_8_max = 1000
gCol_8_min = -1000
End If

dim dMax, dMin

If (Context.Table.Name = "My2DTable") Then
' logfilewrite Context.Table.Name
' logfilewrite Context.Table.Columns.count
' 'logFilewrite Context.Table.Columns
' logfilewrite Cell.Value
' logfilewrite "Column:" &Context.Col
' logfilewrite "D2TabCol:"& D2TabCol
if D2TabCol = 1 then
dMax = gCol_1_max
dMin = gCol_1_min
elseif D2TabCol= 2 then
dMax = gCol_2_max
dMin = gCol_2_min
elseif D2TabCol = 3 then
dMax = gCol_3_max
dMin = gCol_3_min
elseif D2TabCol = 4 then
dMax = gCol_4_max
dMin = gCol_4_min
elseif D2TabCol = 5 then
dMax = gCol_5_max
dMin = gCol_5_min
elseif D2TabCol = 6 then
dMax = gCol_6_max
dMin = gCol_6_min
elseif D2TabCol = 7 then
dMax = gCol_7_max
dMin = gCol_7_min
elseif D2TabCol = 8 then
dMax = gCol_8_max
dMin = gCol_8_min
end if
' logfilewrite "dMax:" & dMax & " dMin:" & dMin

' if gCol_1_max = 3 then
' logfilewrite "gCol_1_max=3 " & gCol_1_max
' else
' logfileWrite "gCol_1_max!=3" & gCol_1_max
' end if

If int(Cell.Value*1000) < int(dMin*1000) then
Cell.Font.Color.SetPredefinedColor(eColorIndexRed)
elseif int(Cell.Value*1000) > int(dMax*1000) Then
Cell.Font.Color.SetPredefinedColor(eColorIndexRed)
else
Cell.Font.Color.SetPredefinedColor(eColorIndexGreen)
End IF

End If
End Sub