Microsoft KB Archive/88912

= VB3 How to Make a Spreadsheet-Style Grid that Allows Editing =

Article ID: 88912

Article Last Modified on 11/18/2003

-

APPLIES TO


 * Microsoft Visual Basic 2.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Professional Toolkit for Microsoft Visual Basic programming system for Windows

-



This article was previously published under Q88912



SUMMARY
The Grid custom control does not provide any text editing capability. However, you can create a spreadsheet-style grid that allows editing by using a picture box and a text box.



MORE INFORMATION
We do not recommend creating a spreadsheet-style grid with a large matrix of text box controls because doing so will slow down your program, and use excessive system resources.

An efficient way to create a grid is to draw vertical and horizontal lines to represent the cells of the grid. Use a single text box to allow editing of the active cell. Check for MouseDown events to move the text box to the currently active cell position, and use the Print method to draw text in a cell when the text box moves away from the cell. Then, store the grid cell values in a two dimensional array, indexed by the column and row.

Code can be added to allow for highlighting areas, using ARROW keys to move between cells, and so on.

Step-by-Step Example
 Start Visual Basic, or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.  Place a picture (Picture1) on Form1, and set its properties as follows:   Property      Value ---  AutoRedraw    True ScaleMode    3 - Pixel Height       2000 Width        3000  Place a text box (Text1) in Picture1 by clicking the text box tool. The mouse pointer turns to cross-hairs. Click and drag inside Picture1 to place a gray rectangle appears in Picture1.  Add the following code to the general Declarations section of Form1: ' Maximum grid size. Const grid_col_max = 10 Const grid_row_max = 20

' Current grid size. Dim grid_cols As Integer Dim grid_rows As Integer

' Current cell position. Dim grid_col As Integer Dim grid_row As Integer

' Grid string contents. Dim grid_text(grid_col_max, grid_row_max) As String

' Grid line positions. Dim grid_line_col(grid_col_max) As Integer Dim grid_line_row(grid_col_max) As Integer

' grid_edit_move. '  Moves the grid edit text box to a new position. '  Sub grid_edit_move (col As Integer, row As Integer) Dim x1 As Integer ' Picture box positions. Dim y1 As Integer Dim x2 As Integer Dim y2 As Integer

' Save text box contents to grid array. grid_text(grid_col, grid_row) = Text1.Text

' Clear current cell. x1 = grid_line_col(grid_col) + 1 y1 = grid_line_row(grid_row) + 1 x2 = grid_line_col(grid_col + 1) - 1 y2 = grid_line_row(grid_row + 1) - 1 Picture1.Line (x1, y1)-(x2, y2), Picture1.BackColor, BF

' Print text box contents to current cell. Picture1.CurrentX = x1 + 3 Picture1.CurrentY = y1 + 3 Picture1.Print Text1.Text

' Set new grid current cell. grid_col = col grid_row = row

' Move text box to new cell. x1 = grid_line_col(grid_col) y1 = grid_line_row(grid_row) w! = grid_line_col(grid_col + 1) - x1     h! = grid_line_row(grid_row + 1) - y1      Text1.Move x1 + 1, y1 + 1, w! - 1, h! - 1

' Copy contents of new cell to text box. Text1.Text = grid_text(grid_col, grid_row) End Sub   Add the following code to form Load event procedure: Sub Form_Load ' Set grid size. grid_cols = 4 grid_rows = 6

' Remove border. Picture1.BorderStyle = 0

' Set column widths and row heights. Dim i As Integer Dim d As Integer d = 0 For i = 0 To UBound(grid_line_col) grid_line_col(i) = d        d = d + 40 Next d = 0 For i = 0 To UBound(grid_line_row) grid_line_row(i) = d        d = d + 20 Next

' Draw grid lines. For i = 0 To grid_cols x2% = grid_line_col(i) y2% = grid_line_row(grid_rows) Picture1.Line (grid_line_col(i), 0)-(x2%, y2%) Next For i = 0 To grid_rows x2% = grid_line_col(grid_cols) y2% = grid_line_row(i) Picture1.Line (0, grid_line_row(i))-(x2%, y2%) Next

Call grid_edit_move(0, 0) End Sub   Add the following code to the Picture1 GotFocus event procedure: Sub Picture1_GotFocus Text1.SetFocus End Sub </li>  Add the following code to the Picture1 MouseDown event procedure: ' The following line should appear on one line. Sub Picture1_MouseDown (Button As Integer, shift As Integer,        x As Single, y As Single) Dim col As Integer Dim row As Integer Dim i As Integer

' Find the cell clicked in. col = grid_col row = grid_row For i = 0 To grid_cols - 1 If x>=grid_line_col(i) And x<grid_line_col(i+1) Then col = i         Exit For End If     Next For i = 0 To grid_rows - 1 If y>=grid_line_row(i) And y<grid_line_row(i+1) Then row = i         Exit For End If     Next

' Move the text box there. Call grid_edit_move(col, row) End Sub </li> Press F5 to run the program. Click a cell and edit the text.</li></ol>

This example is very limited in functionality. Text can be edited in each cell but you must click a cell to move to that particular cell. This article shows a method of creating a grid without tying up a large amount of system resources. Feel free to add code to increase its functionality.

Additional query words: 1.00 2.00 3.00 optimize

Keywords: kbcode KB88912

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.