Lesson 23

The FlexGrid Control

Using FlexGrid as a spreadsheet:

FlexGrid is ActiveX (add to project) bound control (can bind to data control)

Use for invoices, taxes, accounting, parts lists, inventories, amortization tables, etc.

Can select cells, resize columns, align headings, format text, etc in FlexGrid

Rows and Columns:

Rows - horizontal/Columns -vertical

Top most row and left most column reserved for titles (shaded)

Rows property sets num. of rows in table

Cols property sets number of columns

Rows/Columns work like two-dimensional array - first dimension row num., second dimension column num.

(Ex: upper-left cell 0,0)

To place value in FlexGrid use TextMatrix property with cell location and value

Ex:

MSFlexGrid1.TextMatrix(3,1) = “Bob”

will put ‘Bob’ in Row 3, Column 1

MSFlexGrid1.TextMatrix(2,1) = 1500

will put ‘1500' in Row 2, Column 1

Inserting Graphics in Cells:

Add graphics to FlexGrid with Set statement, CellPicture property and LoadPicture function (.ico, .bmp, .wmf)

Ex:

Set MSFlexGrid1.CellPicture = LoadPicture(“h:coins.wmf”)

Does not auto resize cell for picture

Manually adjust with RowHeight and ColWidth properties

Ex:

MSFlexGrid1.RowHeight(1) = 2000

MSFlexGrid1.ColWidth(1) = 2000

Put above Set statement to show picture

Selecting Cells:

To format cells, must select

Select cells with Row and Col property

Ex:

MSFlexGrid1.Row = 1

MSFlexGrid1.Col = 1

Select range in code by setting start/end point for selection

Start point is cell last selected with Row/Col properties

End identified with RowSel and ColSel properties

Ex:

MSFlexGrid1.Row = 2

MSFlexGrid1.Col = 2

MsFlexGrid1.RowSel = 5

MSFlexGrid1.ColSel = 3

Selects Cells from (2,2) to (5,3)

To format this range, must set

MSFlexGrid1.FillStyle = flexFillRepeat

allows FlexGrid to format more than one cell at a time (default is flexFillSingle)

Formatting Cells:

Bold, italic, underline, alignment, font & size, foreground & background colors

Note table p.562 for properties

Adding New Rows:

AddItem method like ListBox/ComboBox to add rows

List item to add separating each column with vbTab (tab character)

Ex:

Dim Row as String

Row = vbTab & “Soccer ball” & vbTab & “W17-233" & vbTab & “34.95"

MSFlexGrid1.AddItem Row

Adds new row with information entered

Using FlexGrid for Database Records:

Don’t add FG entries one at a time (slow)

Connect to Data control or assign in loop

Data control can be connected to FlexGrid with DataSource property

FlexGrid autofills with database records

Advanced Sort and Search Options:

Sort with FlexGrid’s Sort property

Uses key column & directional sorting argument

To sort and indicate direction, select column (shown above) and use Sort

Ex:

MSFlexGrid1.Sort = 1 (or other 0-9)

1 (generic ascending)

2 (generic descending)

3 (numeric ascending)

4 (numeric descending)

Searching requires code (Nested For Next loop and InStr function to compare each cell to search string) Note Code p.571

Add Progress bar if file large/takes time

Other Notes:

Note: With Statement for repeated object properties (flex grid p.564)/End With

Note: FlexGrid AllowUserResizing property set to 1 (FlexResizeColumns) to allow user to change width of columns

Supress row/column heading by set FixedRows/FixedCols property to 0

Summary:

Adding FlexGrid ActiveX Control

Assigning to FlexGrid cells

Inserting Graphics in Cells

Selecting Cells

Formatting Cells

Add New Row to Table

Sort Contents of FlexGrid

Search FlexGrid

Look at Quick Reference p. 576