Why Bother?
That is a question many would ask. Why bother using a poorly designed, poorly manageable control, such as Microsoft's Grid, when other, easier and more powerful alternatives exsist? My question is why not? The Grid ships with every available version of Visual Basic (from the 16-bit standard to the 32-bit enterprise), so you already own it. You don't have to go out and spend a couple hundred dollars on an alternative. Secondly, look at the size of the control itself. The 32-bit version weighs only 88 KB, so it's quite small. Finally, look at the potential. Much of the Grid's power is not in the control itself, but lies in the events and procedures of the control -- things you make it do. Never be afraid to crank out a few lines of code to save yourself some money.
Problems
One of the first things that stray people away from using the Grid is it's inability to perform in-cell editing. In the past, there have several attempts to overcome that disability. One of the most notable procedures has been to place a text box over the Grid, to fit the size of the cell. While the technique works, it should never be used, as it has two major flaws.
The first of these flaws show up when the user re-sizes the cell, while they are editing. The size of the text box would remain the same, while the size of the cell would be larger, or smaller. In that case, the text box would overlap another cell -- very unprofessional.
The second flaw, probably the most major, occurs if the grid has scroll bars. If the user moves a scroll bar, while editing, the text box stays put, while the grid cells move behind it. Wouldn't that look nice? The people that developed the text box method knew this, too, but they didn't tell you. The example I saw in the Developer Network intentionally shut off the scroll bars.
Taking a Different Approach
I recently ran across this problem while developing one of my own applications. I needed a way for the user to enter information directly into a grid, so that it could be used later by my application. At first, I tired to use the above method, but I got hung-up on the flaws. I needed to be able to use the scroll bars, and I wanted to let the user re-size the cells.
img src="demo0496.gif">
I then began looking at different applications that used grids. The answer came to me while I was looking at the Microsoft Works 3.0 spread sheet. This spread sheet has a text box positioned just above the "grid". The text box acts as a source of input. When the text box has the focus, it transfers it's Text property to the grid's Text property. If the grid itself, has the focus, it transfers any key strokes directly to the grid's text property. This method looks like in-cell editing, but really is not. I know this sounds confusing, but the code is really quite simple. Here is how it's done:
Note: The code used in this article is available in the Toolbox's CodeVault. The file UI-0496.ZIP is a Visual Basic 4.0 (32-bit) project that contains a sample application that fully demonstrates the techniques in this article.
Private Sub grdTable_KeyPress(KeyAscii As Integer) Dim x As Integer 'If you want line breaks in your text, then 'disable this line, but you will need to set the 'MultiLine property of txtEdit to True If KeyAscii = 13 Then Exit Sub 'Void ENTER key If KeyAscii = 8 Then 'BACKSPACE key, remove last character If grdTable.Text = "" Then Exit Sub x = Len(grdTable.Text) - 1 grdTable.Text = Left$(grdTable.Text, x) Else grdTable.Text = grdTable.Text & Chr$(KeyAscii) End If txtEdit.Text = grdTable.Text 'update text box End Sub
The code, first, does a series a checks to determine if either the ENTER or BACKSPACE keys have been pressed. If ENTER is pressed (ASCII code 13), it simply ignores it and exits. If BACKSPACE is pressed (ASCII code 8), it takes the last character off of the Text property of the current cell, then exits. If the Text property is empty, it exits, to avoid an error.
If neither key is pressed, KeyAscii is translated into a character, then is added to the Text property of the current cell. The value of the text box is also updated at this time.
Note: The Row and Col properties specify the current cell in a Grid. You can specify the current cell in code, or a user can change it at run time using the mouse or the arrow keys. The Text property references the contents of the current cell. (Visual Basic Help, Microsoft Corp., 1995)
Private Sub grdTable_KeyDown(KeyCode As Integer, Shift As Integer) Select Case KeyCode Case 46 'If DELETE is presses 'If the user presses the DELETE key, then erase 'the contents of the cell grdTable.Text = "" txtEdit.Text = grdTable.Text Case 37, 38, 39, 40 'any arrow key 'if the user chnages cells with keyboard, 'clear out the text box. txtEdit.Tag = "SkipChange" 'required so contents of the grid text isn't deleted txtEdit.Text = "" End Select End Sub
A nice feature I added, allows the user to delete the entire contents of a cell using the DELETE key. This is a feature of most spread sheets, and is quite simple to implement - - if the DELETE key (keyboard code 46) is pressed, blank out the Text properties of both the Grid and text box.
The next chunk of code determines if an arrow key was pressed. If an arrow was pressed, the contents of the text box has to be deleted, but, before we do that, we must tell the text box not to delete the contents of the grid cell. This is done using the Tag property of the text box. You will see how this used later.
Private Sub grdTable_Click() and Private Sub grdTable_RowColChange() 'Put the cell's text into txtEdit txtEdit.Text = grdTable.Text End Sub
Private Sub txtEdit_Change() If txtEdit.Tag = "SkipChange" Then txtEdit.Tag = "" Exit Sub Else grdTable.Text = txtEdit.Text End If End Sub
The first thing that is tested is the Tag property. If it's value is "SkipChange", it means that the user changed cells using the keyboard, therefore, the change in the text box should be ignored, and the contents of the current cell shouldn't be changed. The last item of business updates the contents of the Grid with that of the text box.
The Finishing Touches
Of course, there are several features that can be added to this method. For example, the code for this article (see above) has four command buttons that allow the user to add and delete rows and columns on-the-fly. The escape key could also be used as an "undo" option that restores the previous value of a cell. The possibilities are endless.
The Microsoft Grid Control is a great tool for displaying data in a spread sheet fashion, but it lacks several key features. In the past, several attempts have been made to add more power to the Grid, but they have several flaws. Hopefully, using the method described in this article you too can add more punch to the Grid control!
[ About Ryan Heldt: In addition to writing The Usable Interface, Ryan also maintains The Toolbox Visual Basic Home Page. He is also working on World Writer, a HTML authoring tool for Windows. Ryan can be reached at rheldt@netins.net]
Click here to go back to the April '96 Article Index