How to Make a Spreadsheet-Style Grid that Allows Editing

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.