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

If the user enters numbers in the cells of column 2, we’ll add those values together in a running sum that appears at the bottom of that column, just as in a real spreadsheet program. To enter a number in a cell, the user can click the flex grid, which sets the grid’s Row and Col properties. Then, when the user types, we can add that text to the cell:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

    MSFlexGrid1.Text = MSFlexGrid1.Text + Chr$(KeyAscii)
...
End Sub

This represents one way of letting the user enter text into a grid, but notice that we’d have to handle all the editing and deleting functions ourselves this way; see the next topic in this chapter to see how to use a text box together with a flex grid for data entry.

Now that the user has changed the data in the spreadsheet, we add the numbers in column 2 this way:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
    Dim intRowIndex As Integer
    Dim Sum As Integer

    MSFlexGrid1.Text = MSFlexGrid1.Text + Chr$(KeyAscii)

    MSFlexGrid1.Col = 2
    Sum = 0

    For intRowIndex = 1 To MSFlexGrid1.Rows – 2
         MSFlexGrid1.Row = intRowIndex
         Sum = Sum + Val(MSFlexGrid1.Text)
    Next intRowIndex
...

Note that each time you set the Row and Col properties to a new cell, that cell gets the focus. Because we want to place the sum of column 2 at the bottom of that column, that’s a problem. When we place the sum there, as users type the digits of the current number they’re entering, the focus would keep moving to the bottom of the column. To avoid that, we save the current row and column and restore them when we’re done displaying the sum:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
    Dim intRowIndex As Integer
    Dim Sum As Integer

    MSFlexGrid1.Text = MSFlexGrid1.Text + Chr$(KeyAscii)
    OldRow = MSFlexGrid1.Row
    OldCol = MSFlexGrid1.Col
    MSFlexGrid1.Col = 2
    Sum = 0

    For intRowIndex = 1 To MSFlexGrid1.Rows – 2
        MSFlexGrid1.Row = intRowIndex
        Sum = Sum + Val(MSFlexGrid1.Text)
    Next intRowIndex

    MSFlexGrid1.Row = MSFlexGrid1.Rows – 1
    MSFlexGrid1.Text = Str(Sum)
    MSFlexGrid1.Row = OldRow
    MSFlexGrid1.Col = OldCol
End Sub

And that’s it. Now the user can type numbers into the spreadsheet, and we’ll display the running sum, as shown in Figure 12.15. We’ve created a spreadsheet program using a flex grid control.

Figure 12.15  Adding numbers in the flex grid spreadsheet program.

The code for this example is located in the spreadsheet folder on this book’s accompanying CD-ROM. Note that in this case we had to handle text entry ourselves, and we didn’t let the user delete characters or perform other edits like cut and paste. We can do that if we use a text box for character entry, and we’ll see how to do that in the next topic.

Typing Data Into A Flex Grid

In the previous topic, we saw how to work with data in a flex grid and how to use the KeyPress event to support rudimentary text entry. Microsoft, however, suggests you use a text box for text entry in a flex grid—but how are you supposed to do that?

The way you do it is to keep the text box invisible until the user selects a cell, then move the text box to that cell, size it to match the cell, and make it appear. When the user is done typing and clicks another cell, you transfer the text to the current cell and make the text box disappear.

Why Microsoft didn’t build this into flex grids is anybody’s guess—perhaps because many flex grids are not supposed to support text entry, and that functionality would just take up memory. However, we can do it ourselves.

To see how this works, add a text box to a form, and set its Visible property to False so it starts off hidden. Then add a flex grid to the form and give it, say, 10 columns and 10 rows. We can label the columns with letters and the rows with numbers, as is standard in spreadsheets (note that we use the Visual Basic Chr and Asc functions to set up the letters, and that we enter the text directly into the flex grid using its TextArray property):

Sub Form_Load()
    Dim intLoopIndex As Integer

    For intLoopIndex = MSFlexGrid1.FixedRows To MSFlexGrid1.Rows – 1
        MSFlexGrid1.TextArray(MSFlexGrid1.Cols * intLoopIndex) =_
            intLoopIndex
    Next

    For intLoopIndex = MSFlexGrid1.FixedCols To MSFlexGrid1.Cols – 1
        MSFlexGrid1.TextArray(intLoopIndex) = Chr(Asc("A") +_
            intLoopIndex – 1)
    Next
End Sub

To select a cell, the user can click it with the mouse. When the user starts typing, we can add the text to the text box this way:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

    Text1.Text = Text1.Text & Chr(KeyAscii)
    Text1.SelStart = 1
...

We also move the text box to cover the current cell and shape it to match that cell using the flex grid’s CellLeft, CellTop, CellWidth, and CellHeight properties:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

    Text1.Text = Text1.Text & Chr(KeyAscii)
    Text1.SelStart = 1

    Text1.Move MSFlexGrid1.CellLeft + MSFlexGrid1.Left,_
        MSFlexGrid1.CellTop + MSFlexGrid1.Top,     MSFlexGrid1.CellWidth,_
        MSFlexGrid1.CellHeight
...
End Sub

Finally, we make the text box visible and give it the focus:

Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)

    Text1.Text = Text1.Text & Chr(KeyAscii)
    Text1.SelStart = 1

    Text1.Move MSFlexGrid1.CellLeft + MSFlexGrid1.Left,_
        MSFlexGrid1.CellTop + MSFlexGrid1.Top, MSFlexGrid1.CellWidth,_ 
        MSFlexGrid1.CellHeight
    Text1.Visible = True
    Text1.SetFocus
End Sub