In Figure 9.9, the cells of the first row and column have a gray background because they are meant to be used as titles for the corresponding columns and rows. The unique feature of these cells is that they don’t scroll along with the rest of the cells, so the titles always remain visible. You can change the number of the title rows and columns with the FixedCols and FixedRows properties, use the Format String property to set up the control’s headers, and adjust column width and row height with the AllowUserResizing property.
FixedCols. FixedRows Properties The most common value for these two properties is 1, which translates into one fixed row and one fixed column. The fixed row and column contain titles that can be assigned with any of the methods mentioned earlier or with the help of the FormatString property (discussed in next section): Other than their different background color and the fact that they don’t scroll, the fixed cells are the same as the other cells of the grid.
Another characteristic of the title row and column is that clicking one of their cells selects the entire row or column (see Figure 9.10). Clicking the very first fixed cell of the grid, in the upper left corner, selects the entire grid. This behavior can be changed by setting the AllowBigSelection property to False instead of True (its default value).
FormatString Property This property can be assigned a string variable that sets up the control’s column width, alignments, and fixed row and column text. The FormabString property is made up of segments separated by pipe characters (I). The text between two pipes defines a new column or row, and it can contain text and the alignment characters shown in Table 9.5.
The text becomes the column’s header, and its width defines the width of the column. The semicolon denotes that the text following it applies to the next row. The text also becomes the row’s header, and the longest string defines the width of the fixed column. Similar to column titles, successive row titles are separated with the pipe symbol.
Figures 9.11, 9.12, and 9.13 demonstrate the use of the FormatString property to set up a grid with a fixed row, column, or both. These figures were created with the FString application, which you will find in this chapter’s fold.er on the CD.
The header row of Figure 9.11 is created with the following format string:
s$ – ‘<Country IAthletesl | Gold | ^ SilverI ^Bronze’
MSFlexGrid1.FormatString = s$
Successive column headers are separated by the pipe character. Some spaces are inserted before and after the titles to create some space between the headers and the cell dividers.
The header column of Figure 9.12 is created with a similar format string:
s$ – ‘;Country IAthletesl | Gold | ^ SilverI ^Bronze’
MSFlexGrid1.FormatString = s$
This string starts with the semicolon, which indicates that the following entries are row headers. After the semicolon, the pipe symbol is used to delimit each row’s header.
In the grids in Figures 9.11 and 9.12, the user is expected to enter the country names along with the other information.
The grid in Figure 9.13 displays the headers of the previous examples, as well as the country names; the user needs to supply only the numeric data .
This grid’s headers are set up with a format string that is a combination of the two previous format strings:
Notice again that there’s only one semicolon in the string, which denotes that all the entries that follow are row headers. The country names are separated by the pipe symbol, and the longest country name determines the width of the fixed column.
Each column in the grid may have its own width, which is controlled by the Co/Width property. To set the width of the first column (the title column), use the following statement:
Grid1.ColWidth(0)=500
The Width’s value is expressed in twips, and as mentioned earlier, there are 20 twips in a point. The previous statement sets the width of the first column to approximately 25 points. Likewise, each row can have a different height, which is controlled by the RowHeight property, whose syntax is quite similar, It’s quite common to use the TextWidth property to set up the width of the column ..The MSFlex- Grid control doesn’t have a TextWidth property, so you must set the Form’s Font property to the same value as the grid’s Font property and use the Form’s Text- Width property. Open the FString project in the Visual Basic IDE and examine its code; it uses the ColWidth property to set the width of the grid’s columns.
AllowUserResizing Property The user can change the width of columns and height of rows at runtime by dragging the column and row separators with the mouse. If you place the mouse over a dividing line between two columns (or rows) m:.the title section of the grid, it assumes the shape of a double arrow indicating that it can be dragged to resize a row or column of cells. To disable row and/or column resizing, set the AllowUserResizing property to one of the values shown in Table 9.6. .
Working with Multiple Cells
One of the most useful aspects of a spreadsheet is the ability to select multiple cells and perform certain operations on them, such as copying, pasting, and formatting. Unfortunately, you can’t insert formulas for calculated fields in an MSFlexGrid control, but you can perform simpler, useful operations such as sorting the grid’s rows, as you will see later in the section “Sorting the Grid.”
Properties for Selecting a Range of Cells The user can select a range of cells with a click-and-drag operation over multiple cells. By clicking a fixed column (or row) cell, the user can select the entire column (or row). The selected range is always a rectangle, and the coordinates of the first selected cell (where the mouse button is pressed) are given by the properties Rowand Col. The coordinates of the last selected cell (where the mouse button is released) are given by .the properties RowSeI and ColSel. If you set these properties, you can select a range from within your code. The following statements select the month names on the grid previously seen in Figure 9.9:
Grid.Row = 1
Grid.Col = 0
Grid.RowSel = 12
Grid.ColSel = 0
The selected range is highlighted and all its cells appear in reverse background color except for the first one. This cell is distinguished from the rest because it also happens to be the active cell, or the cell that was clicked at the start of the click-and-drag operation. You can also restrict the way cells are selected with the SelectionMode property; its settings are shown in Table 9.7.
FillStyle Property The simplest operation you can perform on a range of cells to assign an initial value to them. Normally, the value assigned to the control’s Text property applies to the active cell. However, the property FillStyle determines whether a value is assigned to the active cell or to the entire range of selected cells. The FillStyle property is a property of the control, and its values are shown in Table 9.8.
The same property can be used to specify whether other cell properties, such as alignment or background color, will be applied to the active cell or to an entire range of cells.
Clip Property This property holds the contents of the selected cells on the grid. Values of adjacent cells on the same row are delimited by a tab character (Chr$(9)), and successive rows are delimited by a new line or carriage return character (Chr$(13)). The most common operation of the Clip property is to transfer data to and from the Clipboard. The following statement transfers the values of the selected cells to the Clipboard, where they are stored as text:
Clipboard.SetText Grid.Clip
To paste these cell values to another area of the control, first select the destination of the paste operation, and then use, the following statement:
Grid.Clip = Clipboard.GetText
Observe that the contents of the Clipboard are pasted only on the selected cells, that is, on the cells that are part of the current Clip property. If the number of selected cells is larger than the number of cells in the Clip property, some cells’ won’t be affected. On the other.hand, if there are fewer selected cells than the Clipboard’s entries, some of the Clipboard’s data will not end up in the grid.
The MSFlexGrid control doesn’t provide a method for storing its contents to a disk file that’s similar to the SaveFile and LoadFile methods of the RichTextBox control. The simplest way to store the contents of an entire grid to a disk file is to use the Clip property. First, select the entire grid, then write the Clip property (which by now holds the contents of the entire grid) to a file. The following statments store the current grid to the Grid. GRD file (the extension is arbitrary; no known file type uses it):
Open “Grid.GRO” For Output As #1
Write #1, Grid.Clip
Close #1
This method is used in the FlexGrid application to implement the Save and Save As commands.
You can also prepare data to append to a spreadsheet from within another application. First, create a long string with data, using the Tab character to delimit success sive values on the same row and the carriage return character to delimit successive columns. Then, assign this string to the MSFlexGrid control’s Clip property.
Cell Appearance and Alignment
Another group of properties relate to the font, style, and color of the cells. With the MSFlexGrid control, you have the flexibility to control the appearance of individual cells with the following properties:
- CellFontBold and CellFontItalic set (or return) the style of the text in the selected cells. To change the style of multiple cells, you first select the cells and then set the FillStyle property to flexFillRepeat.
- CellFontName sets (or returns) the font to be used in rendering the text in the selected cells.
- CellFontSize sets (or returns) the font’s size in points.
- CellFontWidth sets (or returns) the width of the selected cell(s) in points.
When you change the font’s width, its height remains the same, as specified by tIle CellFontSize property (or whatever font size you specified at design time in the control’s Properties window).
CellFontSize, CellFontWidth Properties To find out how these properties affect the appearance of the text on an MSFlexGird control, experiment with the Font- Size application in the Grid folder on the CD (shown in Figure 9.14). The two Combo – boxes at the bottom of the form let you set the size-of both properties in points.
As you change the CellFontSize property, both the width and height of the characters change. Change the CellFontWidth property, and only the width of the characters changes. In general, when you set the width of the text, you should consider its height. If you have a large amount of data to display, you can set both the Cell- FontSize and CellFontWidth properties to squeeze more data in the available area by making the numbers narrow, yet easy-to-read.
CeIlForeColor, CeIlBackColor Properties To change the color of the text or the background color in the selected cell(s), use the following properties. The value of these properties can be an RGB Color value or a QB Color value.
- BackColorBgk returns or sets the background color of various elements of the control (the area outside the grid).
- ForeColorFixed returns or sets the foreground color of the fixed rows and columns.
- BackColorFixed returns or sets the background color of the fixed rows and columns.
- ForeColorSel returns or sets the foreground color of the selcted cells.
- BackColorSel returns or sets the background color of the selected cells.
You will see how these properties are used in the FlexGrid application later in the chapter. The FlexGrid application allows the User to select a range of cells and set their foreground ~d background colors independently of the rest of the grid.
CellAlignment Property You can give each cell in the grid a different alignment by using this property. You can use it to set the alignment of the active cell or the range of selected cells (as long as the FillStyle property is set to True, of course), and it can take on one of the values listed in Table 9.9.
As you can see, the cell’s contents can be aligned both vertically and horizontally. To align a range of cells from within your code, follow these steps:
- Select the range by setting the Row, Co), SelRow, and SeICol properties to the appropriate values.
- Make sure that the FillStyle property is set to True.
- Assign one of the values in Table 9.9 to the CellAlignment property.
ColAlignment Property If you want to align an entire column, you can use this property instead of CellAlignment. It’s an array with one element per column. Its syntax is as follows:
MSFlexGridl.ColAlignment(column) = value
The column argument is the number of the column whose alignment you want to set. The ColAlignment property may take on any of the values previously shown in Table 9.9. The following statement centers the text in the first (most likely fixed) column, regardless of the current selection:
MSFlexGrid1.ColAlignment(0) = 4
Notice that there is no RowAlignment property to set the alignment of the cells of an entire row.
Sorting the Grid
Nearly every application you will develop with the MSFlexGrid control will require a sorting feature. The control’s Sort property does just that. Sort is not a method, as you might expect, but a property. Each time the Sort property is set to a value, the selected rows of the grid are sorted according to selected criteria. If no rows are selected, the entire grid is sorted.
Sort Property Before setting this property, you must select one or more columns, which are the keys used in the sorting. The keys are the values according to which the rows of the grid are sorted, and they must be the elements of one or more rows. If you select a single column, the (selected) rows are sorted according to the entries of this column. If you select multiple columns, their entries are combined to create the sort key. If you set the Col property to 1 and the ColSeI property to 3, the entries in the first column are the primary sorting keys. If two or more rows have the same primary key, they are sorted according to their values in the second column. If these keys are identical, the same cells in the third column are used as keys
The syntax of the Sort property is as follows:
MSFlexGrid1.Sort = value
The variable value can be one of the values shown in Table 9.10.
If the Sort property is set to 4, for example, the rows are sorted according the numeric value of the selected colurnn(s). The Sort menu of the FlexGrid application (discussed later in this chapter) contains commands for all types of sorting operations, as shown in Figure 9.15. When an option from this menu is selected, the code assigns the appropriate value to the Sort property and the selected rows are sorted instantly.
Sort’s Custom Setting (9) and Compare Even, All but the last value of the Sort property are self-explanatory. When Sort is/set to Custom, the program must specify the order of the columns through a series of Compare events. The Compare event is peculiar in that it’s triggered a number of times. It’s triggered as many times as there are rows in the grid, and it prompts your program to specify which of two columns will appear first.
The first Compare event determines the order of the first and second rows. When the first two columns are in place, the Compare event is triggered for the second and third rows, then for the third and fourth rows, and so on. As you can imagine, the custom value of the Sort property is slow compared with the other values, but it’s the most flexible value. The Custom setting of the Sort property allows you to sort rows according to any criteria and to use nonadjacent columns as sorting keys.
The syntax of the Compare event is as follows:
Sub MSFlexGridl.Compare(row1, row2, cmp)
In this event’s handler, your code must decide how the rows rowl and row2 will be sorted and set the cmp argument according to one of the values in Table 9.11.
The Compare event arranges two rows at a time, so if the Sort property is set to 9, it’s triggered many times. The first time it’s triggered, the row1 argument is 1 and the row2 argument is 2 (assuming that the first row is fixed). The second time it’s triggered, the two arguments are 2 and 3; the third time, they are 3 and 4; and so an up to the rows Rows-1 and Rows. Depending on the type of sorting you want to achieve, you may have to set the Sort property to 9 several times, however, your code will become really slow.
