Returns or sets the format used to display numeric values.
Syntax
[form!]VSFlexGrid.ColFormat(Col As Long)[ = value As String ]
Remarks
This property allows you to define a format to be used for displaying numerical, Boolean, or date/time values. The syntax for the format string is similar but not identical to the syntax used with Visual Basic's Format command. The syntax is described below:
Formatting Numbers:
The characters used to format numerical values are as follows:
|
Char |
Description |
|
$ |
A locale-dependent currency sign is propended to the output. |
|
, |
Locale-dependent thousand separators are added to the output. |
|
( |
Negative values are displayed enclosed in parentheses. |
|
. |
The number of decimals is determined by the number of "0" or "#" characters after the decimal point. |
|
% |
The value is multiplied by 100 and followed by a percent sign. |
|
,. |
The value is divided by 1000 and displayed with thousand separators. |
The string "Currency" is also recognized by the control. It formats numbers using the system's currency settings (specified with the Control Panel.)
Formatting Boolean Values:
If a column's ColDataType property is set to flexDTBoolean, the control will display checkboxes by default. If you want to represent the Boolean values in other ways (for example, True/False, On/Off, Yes/No), then set the ColFormat property to a string containing the values you want to display for True and False values, separated by a semicolon. For example:
fg.ColDataType(2) = flexDTBoolean
fg.ColFormat(2) = "Yes;Not Available" ' or "True;False", "On;Off", "Yes;No", and so on
Formatting Dates and Times:
The characters used to format date/time values is the same as the one used with Visual Basic's Format command (including predefined strings such as "Short Date").
The ColFormat property does not modify the underlying data, only the way it is displayed. You may retrieve the data using the Cell(flexcpText), Text, or TextMatrix properties. You may retrieve the display text using the Cell(flexcpTextDisplay) property. The example below shows several types of format and their effect:
Private Sub Form_Load()
' format numbers
fg.ColFormat(1) = "#,###.##" ' number with thousand separators
fg.ColFormat(2) = "#.###%" ' percentage
fg.ColFormat(3) = "#,.##" ' thousands
fg.ColFormat(4) = "Currency" ' thousands
' format Booleans
fg.ColDataType(5) = flexDTBoolean
fg.ColFormat(5) = "Probably;Hardly" ' Boolean
' format dates
fg.ColFormat(6) = "ddd, mmmm d, yyyy"
fg.ColFormat(7) = "Medium Date"
fg.ColFormat(8) = "Medium Time"
' set some cells
fg.TextMatrix(1, 1) = 1234.56
fg.TextMatrix(1, 2) = 0.5432
fg.TextMatrix(1, 3) = 125250
fg.TextMatrix(1, 4) = -1234.5
fg.TextMatrix(1, 5) = True
fg.TextMatrix(1, 6) = #7/4/1969#
fg.TextMatrix(1, 7) = #7/4/1969#
fg.TextMatrix(1, 8) = #7/4/1969#
' display results:
Dim i%
Debug.Print "Format"; Tab(20); "Content"; Tab(40); "Display"
Debug.Print "-----------"; Tab(20); "-----------"; Tab(40); "-------------"
For i = 1 To 8
Debug.Print fg.ColFormat(i); Tab(20); _
fg.Cell(flexcpText, 1, i); Tab(40); _
fg.Cell(flexcpTextDisplay, 1, i)
Next
End Sub
This code produces the following output:
Format Content Display
----------- ----------- -------------
#,###.## 1234.56 1,234.56
#.###% 0.5432 54.320%
#,.## 125250 125.25
Currency -1234.5 ($1,234.50)
Probably;Hardly True Probably
ddd, mmmm d, yyyy 04/07/1969 Fri, July 4, 1969
Medium Date 04/07/1969 04-Jul-69
Medium Time 04/07/1969 12:00 AM
When setting this property, the Col parameter should be set to a value between zero and Cols - 1 to set the format for a given column, or to -1 to set the format for all columns.
Data Type
String