MS Flex Grid in VB6

Whenever you are doing any project, always a need arises where you have to display the Data in a Tabular Format. There are several Controls in VB6 Which can take care of such displays. But, I have always found MSFlexGrid come in handy. As the name itself suggests, it is Really a Flexible Grid.

There are 2 ways to display the Data in FlexGrid :

• DataBinding
• Manually

DataBinding is Done just by Addaing a DataControl to the Form and setting the DataSource Property of the Grid to DataControl.
Here I will deal with the other way, Which Includes a bit of Coding. It’s a visual treat to see the Display of FlexGrid by changing the Font/Color Properties.
Here I will take a Simple EmpMaster Table which Contains these Fields:

• EmpNo (Numeric)
• EmpName (Text(100))
• DOJ (Date)
• BasicSalary (Number)
• Allowances (Number)

Say, you have all the data loaded in the database.
First of all, Open a New Project Add this in Reference:”Microsoft Active-X Data Objects Library 2.0” (or any higher version if u have.).
Now to Add MSFlexGrid: Goto Menu Project>Components, Select
“MSFlexGrid Control 6.0” Check it and Click OK.
Now the Grid will be loaded in the ToolBox.
Add the MSFlexGrid Control to your Form and Rename it as “Grd”

'Open the Connection say “AConn”, Declare a ADODB.Recordset Object say RST. And query the EmpMaster Table:

Dim TCurr As Currency

Dim i As Long

Dim sSQL As String

Dim RST As New ADODb.RecordSet

sSQL = “Select * From EmpMaster Order By EmpName

RST.Open sSQL,AConn,dbOpen, adOpenStatic, adLockReadOnly

'Load the Grid Using This Code:

i = 0

With Grd

    .Rows = 1

    .Cols = 6

    .TextMatrix(0,0) = “EmpNo”

    .TextMatrix(0,1) = “Name”

    .TextMatrix(0,2) = “JoinDate”

    .TextMatrix(0,3) = “BasicSalary”

    .TextMatrix(0,4) = “Allowances”

    .TextMatrix(0,6) = “TotalSalary”

     If Not RST.EOF Then

         RST.MoveFirst

         Do While Not RST.EOF

              i = i + 1

              Rows = i + 1

              .TextMatrix(i, 0) =RST(“EmpNo”) & “”

              .TextMatrix(i, 1) =RST(“EmpName”) & “”

              .TextMatrix(i, 2) =RST(“DOJ”) & “”

              .TextMatrix(i, 3) =RST(“BasicSalary”) & “”

              .TextMatrix(i, 4) =RST(“Allowances”) & “”

               TCurr = Val(RST(“BasicSalary”) & “”) ) + Val(RST(“Allowances”) & “”)

              .TextMatrix(i, 5) = TCurr

              RST.MoveNext

         Loop

     End If

End With

Well, Now that’s Done, and your Table Data is now shown in the Grid. After Running the form you notice, you need to Increase the Name Column.. Just Wrie this Code:

Grd.ColWidth(1) =2000

You may also notice, you need to Align the Columns. By Defalut in FlexGrid, all the alignment of the Cells depnd on the data they Hold. If a Cell Holds a alpha numeric value starting with a Number, it aligns it to the Right. To Over-Ride this, you can set the Column Alignment, FlexGrid supports all the possible Alignments:

   With Grd

        .ColAlignment(0) = flexAlignCenterBottom

        .ColAlignment(1) = flexAlignCenterTop

        .ColAlignment(2) = flexAlignLeftBottom

    End With

Other possible types are(all names are Self Explanatory):

flexAlignCenterBottom

flexAlignCenterCenter

flexAlignCenterTop

flexAlignGeneral

flexAlignLeftBottom

flexAlignLeftCenter

flexAlignLeftTop

flexAlignRightBottom

flexAlignRightCenter

flexAlignRightTop

Now, Coming To the Best Part Colouring:

You may want To Change the BackColour of the Row Heading to Blue and its Font Colour to White, Check this:

Grd.BackColorFixed = vbBlue
Grd.ForeColorFixed = vbWhite

And Change color of other Rows cells this way:

Grd.BackColor = vbRed
Grd.ForeColor = vbWhite

You want to Make the Heading Font Bold And Bigger:

With Grd

    Dim j As Integer

    .Row =0

    For j = 0 To .Cols – 1

        .Col =j

        .CellFontBold =True

        .CellFontSize =12

        .CellFontName = “Courier New”

    Next

End With

You would want to play with the other CellFontProperties: .CellFontUnderline, .CellFontStrikethrough, CellFontItallic….,

Explicitly, If you want to add a Row after 6th Row:

Grd.AddItem “” , 6

Remove 5th Row(Excluding HeadingRow):

Grd.RemoveItem 5

Increase Height of any Row:

Grd.RowHeight(0) =200

Just Start Using and work will all the properties available, You will definitely Enjoy it.

One of the Major Draw-backs of FlexGrid is, you cannot enter any data. But you can certainly Program to do so. I will take up that in my Next Article.