The MSFlexGrld control is an extremely useful tool for displaying data, but it lacks it basic capability: the user cannot edit a single cell. This limitation can be easily overcome with the help 0.,£ an edit control, namely, the TextBox control. The grid itself provides a few properties and events that make the task of combining the MSFlex-Grid control with· the TextBox control easy.
There are two approaches to editing a grid’s cells. The first is to place a TextBox control on the Form, Each time the user clicks on a cell, the program copies the contents of the active cell to the TextBox control and lets the user edit it. This simple approach is used in the FlexGrid application later in this chapter. The second approach is a bit more elegant, but it volves more complex coding: if you place a TextBox control with the exact same dimensions of the cell being . edited right on top of the cell, the user gets the impression of editing a cell directly . on the grid.
To integrate the TextBoxcontrol with the MSFlexGrid control, use the Cell Width, CellHeight, CellTop, and CellLeft properties of the grid, which determine the current cell’s dimensions and placement on the grid. If you assign the location and size properties of the current cell to the TextBox control, the TextBox control will be overlaid on top of the current cell. At design time, you can place the TextBox anywhere on the Form. It only becomes visible when it’s placed on top of a cell at runtime (see Figures 9.17 and 9.18).
After the text control is placed -exactly on top of the cell, the contents of the current cell are copied to the TextBox. When the user moves to another cell by clicking it, the TextBox’s contents are copied to that cell, and then the TextBox control is placed over it.
VB6 at Work: The GridEdit Project
The GridEdit application, shown in Figure 9.17, implements the second technique described previously for editing the MSFlexGrid control. The Form of the application contains an MSFlexGrid control and a TextBox control.
Figure 9.18 shows the GridEdit application at design time. lhe TextBox control will remain practically invisible at runtime. Because it will be moved from cell to cell users won’t be able to distinguish it from the underlying cell.
In order to program the application, you must first select the events to code. The two events that signal the change of the active cell are the EnterCell and LeaveCell events. No matter how the focus is moved from one cell to another, these events will take place. It’s a good idea, therefore, to place your code behind them.
The EnterCell Event This event takes place every time a new cell is activated in its handler you must:
- Clear the contents of the TextBox control.
- Place the Textbox control over the active cell and resize it to fit the cell.
- Assign the contents of the active cell to the Textbox control’s Text property.
- Move the focus to the TextBox control.
These instructions are translated directly to code with the following lines.
Positioning and Sizing the TextBox Control
Code 9.13:
Notice the lines that check the MouseCol and MouseRow properties to see if the user has clicked on a fixed cell (these two properties return the current mouse .:. po~ition in row and column coordinates). If the user clicks on a fixed cell, the subroutine terminates. Also, the entire column is selected, yet the fixed cell can’t be edited. To disable multiple cell selections, set the AllowBigSelection property to False.
The LeaveCell Event When the user is done editing the current cell and move to another one, the application must copy the text from the TextBox control to the cell just edited. This is done by a single line of code from within the MSFlexGrid’s LeaveCell event:
Private Sub Gridl_LeaveCell()
Grid1.Text = Text1.Text
End Sub
If you run the/application as is, two more problems will surface. First, the Enter- Cell event isn’t fired when the Form is first loaded, so we must invoke it from within the Form’s Load event. To force the code of the EnterCell event to be executed, insert the following lines in the Form’s Load event.
The Form’s Load Event
Code 9 14:
Notice that we don’t have to explicitly call the control’s EnterCell subroutine. Setting a cell’s address from within the code causes the EnterCell event to be executed.
The code presented so far is the absolute minimum required for a functional data entry application based on the MSFlexGrid control. A serious limitation is that the user can’t change the active cell with the help of the keyboard. When the user presses the Enter key, for example, the focus should move to the next cell on the same column or row. No such luck. Not even the arrow keys work.
Although it’s relatively easy to adjust the code tc add more features that will facilitate data entry operations, I’ve omitted them from the example. The GridEdit application’s code demonstrates the basic principles of adding data entry capabilities to the MSFlexGrid control. The properties and aiethods discussed so far will allow you to add advanced editing features to your application.
VB6 at Work: The FlexGrid Project
The FlexGrid application demonstrates most of the methods and properties of the MSFlexGrid control. FlexGrid is a functional spreadsheet application with data entry capabilities, which you can customize for your specific needs. The application’s Form is shown in Figure 9.19.
The top-level menus of the FlexGrid application are as follows:
- File contains the usual Open and Save commands .
- Edit contains commands for exchanging information with the Clipboard.
- Format contains commands for setting the appearance of individual cells font, text color, and background color) and formatting for numeric values.
- Format contains commands for setting the appearance of individual cells font, text color, and background color) and formatting for numeric values.
- Sort contains commands to sort selected rows in all possible ways.
- Align contains the commands for left-aligning, right-aligning, and centering the contents of selected cells.
Data Entry In FlexGrid The code for data entry in FlexGrid uses a technique different from the one described earlier in GridEdit. GridEdit adds data entry capabilities to the MSFlexGrid control using a TextBox control that is placed over the cell being edited, In the FlexGrid application, the data entry mechanism relies on a TextBox control above the grid.
Each time the user selects a cell to edit, the program copies the contents of this cell to the TextBox control, where the editing takes place. The coordinates of the selected cell are displayed on a Label control in front of the TextBox control. When the user is done editing, the contents of the TextBox control are copied to the origi- nal cell, the contents of the next cell are copied to the TextBox control, and the . piocess is repeated. The data entry mechanism is implemented in the MSFlexGrid control’s EnterCell and LeaveCell events.
The EnterCell and LeaveCeIl Events
Code 9.15
On entering a new cell, the program updates the Label control with the coordinates of the newly selected cell and then copies its contents to the TextBox control. lt also selects the text in the control. On leaving the cell, the program copies the TextBox control’s text to the original cell. This code works nicely, but it requires the user to select the next cell with the mouse.
To simplify the data entry process, a few lines of code have been added in the TextBox’s KeyPress event. When the user presses Enter, the TextBox control’s contents are copied to the corresponding cell, and the focus moves to the next cell on the same column. If this cell is the last one in the current column, the focus moves to the first cell of the next column. Or, you can move the focus to the next cell on the same row, and when the end of the row is reached, move to the next row. In other words, Enter signals the user’s intent to commit the changes in the current cell and edit the next.one.
next. You can add more lines in the same event handler to let the user move to other cells with different keystrokes. You can use the Tab key, for instance, to move to the next cell on the same row, or you can use the up and down arrow keys to move to the previous or next cell in the same column. The other two arrow keys are reserved for editing the text and can’t be used for navigation purposes. Following is the Text- Box control’s KeyPress event handler as implemented in the FlexGrid application.
The KeyPress Event Handler
Code 9.16:
Saving and Loading Data The MSFlexGrid control doesn’t provide any methods for storing or loading its contents to and from a disk file. To implement the Open, Save, and Save As commands in the File menu, you can select the entire spreadsheet, copy it to a string variable.and then save it with the Write statement. To load an existing file to the grid, use the Input statement.
After the entire grid has been selected, the Clip property holds the values of all cells in the grid, and the problem of saving them to a disk file is reduced to writing a string variable to the file. The grid’s data are stored in files with the extension GDT (for Grid Data) so that the FileOpen and FileSave dialog boxes can use default extension unique to this application.
The Save As Command
Code 9.17:
As.you can see, most of the lines set up the FileSave common dialog box. The code that actually extracts the data and writes them to the file consists of a few lines that select the entire grid (by calling the Select All command of the Edit menu), assign the clip property to the all Cells variable, and then write to a disk file. The Save command uses the openFile global variable to save the grid’s contents without prompting the user for a filename. (The FreeFile() function returns the next available file handle and is described in the File input/Output tutorial on the CD.) The Open command is quite similar: it reads the string variable from the disk file, selects the entire grid, and then assigns the all Cells variable to the control’s Clip property.
The Edit Menu The FlexGrid application’s Edit menu contains the usual Cut, Copy, and Paste commands, which manipulate the control’s Clip property. The Copy command extracts the selected cells from the grid via its Clip property and assigns them to the Clipboard with the SetText method as follows:
As implemented in the preceding code, ‘the Paste command has a rather serious drawback: the copied cells are pasted in the selected range only. In other words, the user must select a range (its dimensions should match the dimensions of the range of cells currently in the Clipboard) and then issue the Paste command.
To improve the Paste command, your program must nrsi examine the strings that holds the Clipboard’s contents. Successive cell values are separated be a Tab character (Chr$(9)), and successive columns are separated by a new line character (chr$03)). Your program must first select a range with , as many Column as there are cells in a row and as many rows as clipboards strings are the assign the variable to the Clip property.
The Format Menu The FlexGrid’s. Format men; contains the? following commands:
- Font lets the user specify the font attribute or the selected cells) through it Font common dialog box.
- Color let: the user specify the text color for the selected cell(s)
- Cell Color lets the user specify the background color for the selected cell(s)
Also included is a group of commands for formatting numeric data, to apply the desired format to the selected cells select one of these commands.
- ### Formats 1802.5 as 1802
- ### Formats 1802.5 as 1802
- #,###.00 Formats 1802.5 as 1,802.50
- $#,###.00 Formats 1802.5 as $1,8050
These commands call the FormatCells() subroutine with the proper argument. subroutine scans the entire range of selected cells and formats each cell with Format$() function.
The Sorted and Align Menus The Sort menu contains a number of nested commands that let the user specify all.types of possible sorting methods with the MSFlexGrirdcontrol except for the Custom sort through the Sort menu’s commands, you can sort the mwf & rows of the grid as strings or numbers and in ascending or descending mws however, the sorting process can be case-sensitive or case-insensitive.
Implementing the Sort menu is simple Each command in this menu assigns the appropriate value to the MSFlexGrid control’s Sort property (see Table 9.10 earlier In this chapter for a list of these values and how they affect the sorting process). The Sort command relies on the User to select the columns to be used as keys with the mouse. To sort all the rows in the gird, the user would select all the key columns by clicking on their fixed cells. To sort a range of rows only, the user would select the cells in the column(s) with the sorting keys.and the rows to be sorted.
The grid rows will be sorted according to the values of the cells in the selected Columns). If part of a column has been selected. only the selected rows will be sorted (see Figure 9.20). Notice that the MSFlexGrid control switches the rows around including the cells of the fixed column, If the titles of the fixed column are not part of the data (in ether words, if they are sequence numbers, as in the FlexGrid application), you should renumber them after each sort operation.
The Align menu contains the’ commands that let the user left-align, right-align, and center selected cells. After the alignment property for a cell is set, it remains effect even if the cell’s contents are edited. The same is true for the font attributes of the cell, but not for the number formatting commands.
Enhancing the FlexGrid Project FlexGrid has most of the functionality you’ll need to write complicated spreadsheet applications. For instance, you can easily add data processing capabilities to the application. Inserting formulas won’t be easy, but you can calculate the average of a range of cells or normalize them. The code for formatting a range of numeric values, which scans the range of the selected cells, is the structure you need. Instead of formatting the cells’ contents, add their values and divide the sum by the number of selected cells. Or you can fill a column with a function of the values of the cells to its left. .
A few commands that automatically fill a range of cells are also quite common. You can implement a command that reads the value of a cell and fills the following cells in the same column with the initial value incremented by a user-supplied step. These values can also be dates. The user can supply the initial day and an increment in days, and your program can fill the following cells with the appropriate dates.
Thus, you can easily add functionality to the FlexGrid control for many practical situations that require a grid like structure for displaying or entering data, as long as you don’t need something like Excel, If you need the data processing capabilities of Excel, especially data processing with formulas, or if you need a built-in programming language, you should look into more specialized spreadsheet controls. In Chapter 15, you’ll see how to program and control Excel from within a Visual Basic application. .
This example concludes the rather extensive discussion of ActiveX controls in this book. The controls in Chapter 8 and this chapter are rather advanced, but they are the makings of elaborate, highly functional user interfaces. Using these controls in your applications will not be easy, but the examples in this book should serve as starting points for many applications.
