Grids provide a nice way to present certain types of spreadsheet information.Unfortunately, the grid in VB does not allow editing. With just a littlebit of code you can simulate editing on top of a grid.
To begin, you need to create a form with a grid and add a hidden textbox. Name the grid control grdFields, and the text box txtEdit.
When the user double-clicks on a cell you need to move the hidden textbox over that cell and make it visible. Use this code in the grid’s DblClickevent:
Sub grdFields_DblClick () miLastRow = grdFields.Row miLastCol = grdFields.Col Call GridEdit(grdFields, txtEdit) End Sub
You’ll need two module-level variables to track which row and columnthe cursor is on. These variables will be used later to replace the informationfrom the text box back into the grid.
To find the location on the screen for the text box, you need to calculatethe cell’s distance from the top and left of the form. You also need toadd each row height and column width individually, because each cell canbe a different size. Add this GridEdit() routine to calculate the positionand move the text box on top of the grid:
Sub GridEdit (grdCurrent As Control, _ ctlEdit As Control) Dim iWidth As Single ' Total Height Dim iHeight As Single ' Total Width Dim iLoop As Integer iWidth = _ grdCurrent.Left + Screen.TwipsPerPixelX iHeight = _ grdCurrent.Top + Screen.TwipsPerPixelY ' Get Total Width For iLoop = 0 To grdCurrent.Col - 1 iWidth = iWidth + grdCurrent.ColWidth(iLoop) If grdCurrent.GridLines Then iWidth = iWidth + (Screen.TwipsPerPixelX * _ grdCurrent.GridLineWidth) End If Next ' Get Total Height For iLoop = 0 To grdCurrent.Row - 1 iHeight = iHeight + grdCurrent.RowHeight(iLoop) If grdCurrent.GridLines Then iHeight = iHeight + (Screen.TwipsPerPixelY * _ grdCurrent.GridLineWidth) End If Next ' Move the Text Box On Top Of The Grid ctlEdit.Move iWidth, iHeight ctlEdit.Height = _ grdCurrent.RowHeight(grdCurrent.Row) ctlEdit.Text = grdCurrent.Text ctlEdit.Width = _ grdCurrent.ColWidth(grdCurrent.Col) ctlEdit.Visible = True ctlEdit.SetFocus ctlEdit.ZOrder 0 End Sub
After you’ve calculated the width and height, use the Move methodto place the text box at the proper location. Next, make the text box visibleand set its ZOrder to 0, to put it on top of the grid. You also need tomove the text from the grid into the Text Box.
After the user finishes editing the text in the text box, you needto put the text back into the grid and hide the text box. Use this in thegrid’s Click event:
Sub grdFields_Click () If txtEdit.Visible Then Call GridReset(grdFields, txtEdit, _ miLastRow, miLastCol) End If End Sub
Check to see if the text box is visible. If it is, you need totake the edited data and put it back into the grid with the GridReset()procedure:
Sub GridReset (grdFields As Grid, _ ctlEdit As Control, iRow As Integer, _ iCol As Integer) Dim iOldRow As Integer Dim iOldCol As Integer iOldRow = grdFields.Row iOldCol = grdFields.Col grdFields.Row = iRow grdFields.Col = iCol grdFields.Text = ctlEdit.Text ctlEdit.Visible = False ctlEdit.Move 0, 0 grdFields.Row = iOldRow grdFields.Col = iOldCol End Sub
When the user clicks on another cell after editing the text box,the grid has been updated to the new row and column. Go back to the lastrow and column where the user was editing, take the contents of the textbox, and put that value into the grid’s cell. You can then make the textbox invisible and move it to an area on the form that is out of the way.
This tip won’t work if the grid has scrollbars, except when scrolledall the way to the left and top. To fix this would require additional loopsto add up the height/width of fixed rows/columns. In that case, the otherloops would need to be adjusted to calculate only from TopRow to Row, ratherthan from 0 to Row – 1 (but I’d want to test it to be sure!).
Also, it goes nuts if the scrollbar is clicked while text box is visible.At any rate, it will work if scrollbars aren’t enabled.