How to Make a Spreadsheet-Style Grid that Allows Editing

ID: Q140889

4.00 WINDOWS

The information in this article applies to:

- Professional and Enterprise Editions of Microsoft Visual Basic,

  16-bit only, for Windows, version 4.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.

2. Place a picture (Picture1) on Form1, and set its properties as follows:

   Property      Value
   -----------------------
   AutoRedraw    True
   ScaleMode     3 - Pixel
   Height        2000
   Width         3000

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

4. 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.
   '
   Public 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

5. Add the following code to form Load event procedure:

   Private 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

6. Add the following code to the Picture1 GotFocus event procedure:

   Private Sub Picture1_GotFocus ()
      Text1.SetFocus
   End Sub

7. Add the following code to the Picture1 MouseDown event procedure:

   ' The following line should appear on one line.
   Private 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

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

Additional reference words: 1.00 2.00 3.00 4.00 vb4win vb416 optimize KBCategory: KBSubcategory: PrgCtrlsStd PrgCtrlsCus

Keywords          : kbcode PrgCtrlsCus PrgCtrlsStd 
Version           : 4.00
Platform          : WINDOWS

Last Reviewed: May 22, 1998