| Introduction
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:
- data entry & validation
- high level reports
- 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:
- separating grouped data with blank rows/columns,
- adding sub/grand-total rows/columns,
- changing the colour of the cell background or text in individual or
multiple cells,
- reacting to the state of the data, e.g. highlighting negative
values,
- 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
Const SCROLL_BAR_WIDTH = 320
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.
|
