Editing Grid Cells

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job