ColFormat Property

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

See Also

VSFlexGrid Control