About   -   Contact   -   Purchase   -   Search   -   What's New

Using the FlexGrid Control to Manage your Data. (1/3)

Using Microsoft's FlexGrid control (MSFLXGRD.OCX) you can create utilities to display, filter, edit, validate and update your data. For example, such utilities could include:

  1. data entry &  validation
  2. high level reports
  3. ported spreadsheet macro applications retaining cell layout & format

Included in VB4(32-bit), VB5 and VB6, are several grid-orientated controls aimed at managing rows and columns of data, however one of the most versatile, hence its name, is the FlexGrid. Most of the other grid objects are specifically designed for data binding, whereas the FlexGrid has many collections of properties, methods and events that lend themselves to several environments in addition to just data-binding.

One of the most powerful applications of the FlexGrid is in managing data from database tables in an unbound state. That is, to populate & edit the FlexGrid and write the contents back to the database in code. This may seem a pointless exercise as objects such Microsoft's Data Bound Grid Control are written specifically for this purpose, except these grids simply display the contents of a recordset, record by record, field by field. Whereas the FlexGid can populate the grid in any manner, examples of which are:

  1. separating grouped data with blank rows/columns,
  2. adding sub/grand-total rows/columns, 
  3. changing the colour of the cell background or text in individual or multiple cells,
  4. reacting to the state of the data, e.g. highlighting negative values,
  5. validating entered data e.g., numeric values, positive values, permitted date ranges etc.

These are just a few of the possibilities available with FlexGrids, we'll take a look at more later on. 

One important point is that in addition to the grid controls available as standard to Visual Basic, many third party grid controls are available that boast impressive features such as embedded combo boxes, picture boxes, custom data validation etc. Many of these are excellent controls, however they all come at a price proportional to the size of their feature list.

That said, most of the functionality found in these third party grids can be accessed with FlexGrids with a little extra code which we'll cover in this article and as well as saving money, you'll gain knowledge of creating and customising your grid controls to suit your specific application and you won't have to remember to distribute those third part runtime dll's that custom controls often require.

Using the FlexGrid

To use the FlexGrid in your application, right click the Toolbox or select the Project menu and choose 'Components'. The Components dialog box appears as shown in Figure 1.

Figure 1: Components Dialog Box

Select Microsoft FlexGrid Control and copy the control onto your form from the Toolbox. The default FlexGrid will appear on the form as shown in Figure 2.

Figure 2: Default FlexGrid Appearance

There are a few simple formatting tips to improve to appearance of the FlexGrid:

First of all, it's good practice to determine the initial number of grid rows and columns using the Row and Col properties respectively. Then set the number of fixed rows and columns (those that hold row and column headings - raised and in grey) with the FixedRow and FixedCol properties respectively. 

To ensure the columns are the correct width to fit inside to FlexGrid, first set a variable to the width of the FlexGrid, allowing for a vertical Scrollbar width. Then use this variable to resize to individual column widths, by dividing it by the number of columns.

The height of individual rows can be changed to accommodate multi-line headings. This is done by simply multiplying the appropriate row height by a scaling factor, in this case two, to double the height.

When increasing the size of a row height, to make sure text uses the extra available space, use the WordWrap property the ensure text continues on the the next cell line.

Regarding writing text to the grid there are two main methods. The first one is using the Additem method, this writes text to an entire row in one action and is therefore useful for adding headings. The vbTab constant is used to distinguish adjacent cells. The second method to populate grid cells with text is to directly address individual cells and using the TextMatrix or Text property and set the cell's text.

An example of grid initialisation code would be as follows:

Dim lngWidth As Long
Dim intLoopCount As Integer

With MSFlexGrid

    lngWidth = .Width - SCROLL_BAR_WIDTH
    .Cols = 4
    .FixedCols = 1
    .Rows = 0
    .AddItem vbTab & "Heading Text One" & vbTab & _
      "Heading Text Two" & vbTab & "Heading Text Three" & _
      vbTab & "Heading Text Four"
    .Rows = 12
    .FixedRows = 1
    .WordWrap = True
    .RowHeight(0) = .RowHeight(0) * 2

    .ColWidth(0) = lngWidth / 4
    .ColWidth(1) = lngWidth / 4
    .ColWidth(2) = lngWidth / 4
    .ColWidth(3) = lngWidth / 4

    For intLoopCount = 1 To (.Rows - 1)
        .TextMatrix(intLoopCount, 0) = "Item " & intLoopCount
    Next intLoopCount
End With

The initialised FlexGrid should appear on the form as shown in Figure 3..

Figure 3: Initialised FlexGrid Appearance

Changing Cell Background & Foreground Colours

The ability to change the individual cell background colour and cell font colour is useful for highlighting particular data states, e.g. values exceeding a specified ceiling, negative values, cells where data cannot be entered etc. To set the background colour of a cell, first reference the cell by setting the Row and Col properties to the appropriate cell, then use the CellBackColor property to set its colour. Similarly use the CellForeColor property to change the colour of the text displayed in a cell. Examples of available colours are found in the FlexGrid's Properties Window under any of the colour properties, especially the palette tab as this displays a wider range of colours. To set a range of cells to a selected colour each cell must be referenced in turn, unless all the cells in the grid are to have their colour set to the same colour in which case the BackColor and ForeColor property can be used which sets the entire grid's colour.

Continue with FlexGrid Article 2.


Top of Page

Legal Notice

Ken Howe 2011