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_will 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 will 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

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

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

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 would 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 are 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 are 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 will display the running sum. We have created a spreadsheet program using a flex grid control.

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 did not 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 grids 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
When the user clicks another cell, a LeaveCell event is generated, and we can take advantage of that event to transfer the text from the text box to the current cell and hide the text box. Note that if the text box is not visible_in other words, the user is just moving around in the flex grid_we do not want to transfer the text from the text box to the current cell, and so we exit the procedure in that case:
Sub MSFlexGrid1_LeaveCell()
If Text1.Visible = False Then
Exit Sub
End If
...
Otherwise, we transfer the text from the text box to the current cell, clear the text box, and hide it:
Sub MSFlexGrid1_LeaveCell()
If Text1.Visible = False Then
Exit Sub
End If
MSFlexGrid1.Text = Text1
Text1.Visible = False
Text1.Text = ""
End Sub
And thats it. Now users can use the text box to enter text in a way that makes it look as though they are entering text directly into the flex grid


Setting Flex Grid Lines And Border Styles
You can set what types of grid lines a flex grid uses with the GridLines property. These can be set at design time or runtime to the following values:
" flexGridNone
" flexGridFlat
" flexGridInset
" flexGridRaised
You can set the grid line width with the GridLineWidth property.
In addition, you can set the BorderStyle property to show a border around the whole control, or no border at all:
" flexBorderNone
" flexBorderSingle
Labeling Rows And Columns In A Flex Grid
The usual convention in spreadsheets is to label the top row with letters and the first column with numbers.

Here some code to do just that (note that we use the Visual Basic Chr and Asc functions to set up the letters and enter text directly into the flex grid using its TextArray property, which holds the grid_s text in array form):
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

TIP: The columns and rows you label in a flex grid are usually colored gray; you set the number of label columns and rows with the FixedCols and FixedRows properties.



Formatting Flex Grid Cells
The Aesthetic Design Department is calling again. Can_t you use italics in that spreadsheet? Hmm, you think_can you?
Yes, you can: flex grid cells support formatting, including word wrap. You can format text using these properties of flex grids:
" CellFontBold
" CellFontItalic
" CellFontName
" CellFontUnderline
" CellFontStrikethrough
" CellFontSize
Besides the preceding properties, you can size cells as you like using the CellWidth and RowHeight properties.

Sorting A Flex Grid Control
The Testing Department is calling again. Your new program, SuperDuperDataCrunch, is terrific, but why can_t the user sort the data in your spreadsheet? Sounds like a lot of work, you think.

Actually, it_s easy. You just use the flex grid_s Sort property (available only at runtime). For example, to sort a flex grid according to the values in column 1 when the user clicks a button, add this code to your program (setting Sort to 1 sorts the flex grid on ascending values):

Private Sub Command1_Click()
MSFlexGrid1.Col = 1
MSFlexGrid1.Sort = 1
End Sub

TIP: Note that when the user clicks a column, that column becomes the new default column in the Col property, so if you want to let the user click a column and sort based on the values in that column, omit the MSFlexGrid1.Col = 1 in the preceding code.

Dragging Columns In A Flex Grid Control
One of the attractive aspects of flex grids is that you can use drag-and-drop with them to let users rearrange the flex grid as they like. To see how this works, we_ll write an example here that lets users drag and move columns around in a flex grid.

When the user presses the mouse button to start the drag operation, we store the column where the mouse went down in a form-wide variable named, say, intDragColumn in the MouseDown event. This event is stored in the flex grid_s MouseCol property:

Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, _
X As Single, Y As Single)
intDragColumn = MSFlexGrid1.MouseCol
...
We also add that variable, intDragColumn, to the (General) declaration area of the form:
Dim intDragColumn As Integer
Then we start the drag and drop operation for the column in the flex grid:
Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, _
X As Single, Y As Single)
intDragColumn = MSFlexGrid1.MouseCol
MSFlexGrid1.Drag 1
End Sub
Finally, when the user drags the column to a new location and drops it, we can catch that in the DragDrop event. In that events handler_s procedure, we move the column to its new location_the current mouse column_using the ColPosition property:

Private Sub MSFlexGrid1_DragDrop(Source As VB.Control, X As Single,
Y As Single)
MSFlexGrid1.ColPosition(intDragColumn) = MSFlexGrid1.MouseCol
End Sub
And that_s it. Now the user can drag and rearrange the columns in our flex grid. To see how this works, we display a database in our flex grid, as shown in Figure 12.17. To see how to do that, take a look at the next topic in this chapter where we use a Visual Basic data control (here, the database we use is the Nwind.mdb database, which comes with Visual Basic). When the user drags a column in our program, a special mouse pointer appears



Connecting A Flex Grid To A Database
We will work with databases later in this book, but because flex grids are often usedwith databases, we_ll take a look at how to connect a database to a flex grid here. To connect a database to a flex grid, follow these steps:
1. Add a data control, Data1, to your form (the data control is an intrinsic control in Visual Basic and appears in the toolbox when you start Visual Basic).
2. Set the data control_s DatabaseName property to the database file you want to use.
This can also be done at runtime, but if you do so, be sure to call the data controls
Refresh method to update that control. In code, the process goes something like this, where we use the Visual Basic App object_s Path property to get the applications path (assuming the database file is stored at the same path as the application):
Data1.DatabaseName = App.Path & "\Nwind.mdb"
Data1.Refresh
3. Set Data1. RecordSource property to the table in the database you want to work with.
4. Set the flex grid_s DataSource property to the data control_s name, which is Data1 here.