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

Last reviewed: January 9, 1997
Article ID: Q88912

The information in this article applies to:

Professional Edition of Microsoft Visual Basic for Windows, versions 2.0 and 3.00
Microsoft Professional Toolkit for Microsoft Visual Basic programming system for Windows, version 1.0

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

1. 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

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

Press F5 to run the program. Click a cell and edit the text.

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.

KBCategory: kbprg kbcode
KBSubcategory: PrgCtrlsStd PrgCtrlsCus
Additional reference words: 1.00 2.00 3.00 optimize

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.