Editing Grid Cells

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:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as