For cases where regular expressions are insufficient to express your formatting requirements, you can use the FetchCellStyle event to customize fonts and colors on a per-cell basis. This event will only be fired for columns that have the FetchStyle property set to True.
For example, you may want to provide color coding for values that fall within a certain range. The following code assumes that the FetchStyle property is True for a single column of numeric data, and handles the FetchCellStyle event to display values greater than 1000 in blue:
| Example Title |
Copy Code
|
|---|---|
Private Sub TDBList1_FetchCellStyle( _
ByVal Condition As Integer, _
ByVal Split As Integer, _
Bookmark As Variant, _
ByVal Col As Integer, _
ByVal CellStyle As TrueDBList80.StyleDisp)
Dim N As Long
N = Val(TDBList1.Columns(Col).CellText(Bookmark))
If N > 1000 Then CellStyle.ForeColor = vbBlue
End Sub
|
|
The Split, Bookmark, and Col arguments identify which cell the list is displaying. The CellStyle argument conveys formatting information from your application to the list. Since the CellStyle argument is a Style object, you can also change a cell's font characteristics in the FetchCellStyle event:
| Example Title |
Copy Code
|
|---|---|
If N > 1000 Then CellStyle.Font.Italic = True |
|
The FetchCellStyle event can also be used to apply formatting to one cell based upon the values of other cells, or even other controls. For example, suppose that you want to:
-
Make the cell text red in column 4 if column 1 minus column 2 is negative.
-
Make the cell text bold in column 7 if it matches the contents of a text box.
In this case, you need to set the FetchStyle property to True for columns 4 and 7, and handle the FetchCellStyle event as follows:
| Example Title |
Copy Code
|
|---|---|
Private Sub TDBList1_FetchCellStyle( _
ByVal Condition As Integer, _
ByVal Split As Integer, _
Bookmark As Variant, _
ByVal Col As Integer, _
ByVal CellStyle As TrueDBList80.StyleDisp)
Select Case Col
Case 4
Dim Col1 As Long, Col2 As Long
Col1 = CLng(TDBList1.Columns(1).CellText(Bookmark))
Col2 = CLng(TDBList1.Columns(2).CellText(Bookmark))
If Col1 - Col2 < 0 Then CellStyle.ForeColor = vbRed
Case 7
Dim S As String
S = TDBList1.Columns(7).CellText(Bookmark)
If S = Text1.Text Then CellStyle.Font.Bold = True
Case Else
Debug.Print "FetchCellStyle not handled: " & Col
End Select
End Sub
|
|
For efficiency reasons, you should only set FetchStyle to True for columns that you plan to handle in the FetchCellStyle event.
 |
Note: The preceding examples use the CellText method for simplicity. However, the CellText and CellValue methods always create and destroy an internal clone of the dataset each time they are called, which may make them too inefficient to use in the FetchCellStyle event. To improve the performance of the list's display cycle, use a Recordset clone to derive the cell text, if available. Unbound applications can access the underlying data source directly, which is generally faster than calling CellText or CellValue. |
If you need to customize fonts and colors on a per-row instead of a per-cell basis, use the FetchRowStyle event, which will only be fired once per row for lists that have the FetchRowStyle property set to True. The syntax for this event is as follows:
| Example Title |
Copy Code
|
|---|---|
Private Sub TDBList1_FetchRowStyle( _
ByVal Split As Integer, _
Bookmark As Variant, _
ByVal RowStyle As TrueDBList80.StyleDisp)
|
|
Although you can use the FetchRowStyle event to implement an alternating row color scheme, an easier and more efficient way to accomplish the same task would be to use the AlternatingRowStyle property, together with the built-in EvenRow and OddRow styles.
The FetchRowStyle event is ideally suited for coloring the entire row of a list based on the value of one or more columns. The following example demonstrates how to do this using a Recordset clone:
| Example Title |
Copy Code
|
|---|---|
Dim RS As Recordset
Private Sub Form_Load()
Data1.Refresh
Set RS = Data1.Recordset.Clone
TDBList1.FetchRowStyle = True
End Sub
Private Sub TDBList1_FetchRowStyle( _
ByVal Split As Integer, _
Bookmark As Variant, _
ByVal RowStyle As TrueDBList80.StyleDisp)
RS.Bookmark = Bookmark
If RS.Fields("Country") = "Germany" Then
RowStyle.BackColor = vbCyan
End If
End Sub
|
|