Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

Adding A Flex Grid Control To A Program

The Program Design Department is calling. Can you whip up a program to display a few tables of data? No problem, you say, I’ll use the Microsoft flex grid control. They ask, and how about spreadsheets? You say, no problem—flex grids can handle that too.

You can add a flex grid to a Visual Basic project easily; just follow these steps:

1.  Select the Project[vbar]Components menu item.

2.  Click the Controls tab in the Components dialog box.

3.  Select the Microsoft FlexGrid Control entry in the Components dialog box.

4.  Close the Components dialog box by clicking on OK. This displays the Flex Grid Control tool in the toolbox.

5.  Add a flex grid control to your form in the usual way for Visual Basic controls, using the Flex Grid Control tool.

6.  Set the flex grid’s Rows and Cols properties to the number of rows and columns you want in your flex grid. You can also customize your flex grid by setting such properties as BorderStyle, ForeColor, BackColor, and so on.

This gives you a blank flex grid control in your program; the next step is to fill it with data. To start doing that, take a look at the next topic in this chapter.

---

TIP:  When you insert a flex grid, you can also connect it to a database. To do this, you create a new data control (it’s an intrinsic Visual Basic control and appears in the toolbox when you start Visual Basic), connect that control to the database (by setting its DatabaseName and RecordSource properties), then set the flex grid’s DataSource property to the name of the data control. We’ll see more about connecting to a database when we discuss the data-bound Visual Basic controls. (See “Connecting A Flex Grid To A Database” later in this chapter.)

---

Working With Data In A Flex Grid Control

You’re writing your new program, SuperDuperDataCrunch, and it’s time to write the code for the spreadsheet part. You can use a flex grid control here—but how do you insert and work with the data in a flex grid? To see how this works, we’ll build a small spreadsheet example program that adds a column of numbers. This will show how to insert and access data in a flex grid, as well as how to handle text insertion direct from the user in a rudimentary way (we’ll see a better method in the next topic in this chapter). Several flex grid properties will help us here:

•  Row—The current row in a flex grid

•  Col—The current column in a flex grid

•  Rows—The total number of rows

•  Cols—The total number of columns

•  Text—The text in the cell at (Row, Col)

We start by adding a flex grid to a form; give it 7 rows in the Rows property and 7 columns in the Cols property. We’ll begin by labeling the column heads with letters and the row heads with numbers, just as you would see in any spreadsheet program.

Flex grids have FixedCols and FixedRows properties, which set the header columns and rows in the flex grid. These columns and rows are meant to label the other columns and rows, and they appear in gray by default (the other cells are white by default). Both FixedCols and FixedRows are set to 1 by default.

We’ll add a column of numbers here, so we can also place labels in the first column of cells, “Item 1” to “Item 6”, and a label at the bottom, “Total”, to indicate that the bottom row holds the total of the six above. These labels are not necessary, of course, but we’ll add them to show that you can use text as well as numbers in a flex grid. These labels will appear in column 1 of the flex grid, and users can place the data they want to add in column 2. The running sum appears at the bottom of column 2, as shown in Figure 12.13.

Figure 12.13  Designing a spreadsheet.

To set text in a flex grid cell, you set the Row and Col properties to that location and then place the text in the flex grid’s Text property. Here’s how we set up the row and column labels in MSFlexGrid1 when the form loads:

Sub Form_Load()
    Dim Items(6) As String
    Dim intLoopIndex As Integer

    Items(1) = "Item 1"
    Items(2) = "Item 2"
    Items(3) = "Item 3"
    Items(4) = "Item 4"
    Items(5) = "Item 5"
    Items(6) = "Total"

    For intLoopIndex = 1 To MSFlexGrid1.Rows – 1
        MSFlexGrid1.Col = 0
        MSFlexGrid1.Row = intLoopIndex
        MSFlexGrid1.Text = Str(intLoopIndex)
        MSFlexGrid1.Col = 1
        MSFlexGrid1.Text = Items(intLoopIndex)
    Next intLoopIndex
    MSFlexGrid1.Row = 0
    For intLoopIndex = 1 To MSFlexGrid1.Cols – 1
        MSFlexGrid1.Col = intLoopIndex
        MSFlexGrid1.Text = Chr(Asc("A&") – 1 + intLoopIndex)
    Next intLoopIndex
 
    MSFlexGrid1.Row = 1
    MSFlexGrid1.Col = 1

End Sub

The rows and labels appear as in Figure 12.14.

Figure 12.14  The flex grid spreadsheet program.

We’ve set up the labels as we want them—but what about reading data when the user types it? We can use the flex grid’s KeyPress event for that:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

End Sub