i will show you how to make MSFlexGrid editible from the user.
you will need to add MSFlexGrid Componet do this by clicking on menu Project->Components-> select Microsoft FlexGrid Control 6.0(SP6) or from same menu click Browse... button and select from Windows\Systrem32\MSFLXGRD.OCX
Lets Create MSFlexGrid table Drow your Table (MSFGTable is mine FlexGrid)
Put a textbox in your form and make it property Visible - > False
So it is not hard at all we need a MSFlexGrid table and we need a Textbox to show on the cells we need to edit
Now lets begin with the form load and Option explicit of our code
In Option Explicit we add 2 variables they are not rly necessary but it help me understand the code better
Private lRow as Long is used to store the selected row of the table throug our code
Private lCol as Long is used to store the selected Column in the grid
They are global variables only for the curent form.
In From_Load sub i will create the MSFlexGrid table prepera the columns put some data in it
i add a counter so i can put some data in the cells. So i make 4 Columns give them names for that we use the first row of the MSflexGrid. Make the first row Fixed so it cant be edited or selected by user and the name of the columns cant be edited.
After we make the columns and name them we put in Some values that is what the for cycle do.
Option Explicit Private lRow As Long 'Selected Row Private lCol As Long 'Selected Column Private Sub Form_Load() Dim i As Integer On Error GoTo err_h With MSFGTable ' Generate 4 columns and 1 fixed row used as Names of the cells .FixedRows = 1 .FixedCols = 0 .Rows = 1 .Cols = 4 .TextMatrix(0, 0) = "Value1" 'First row is for cells names .TextMatrix(0, 1) = "Value2" .TextMatrix(0, 2) = "Value3" .TextMatrix(0, 3) = "Value4" ' Add a data in the cells that will be edited later For i = 1 To 10 .Rows = i + 1 .TextMatrix(i, 0) = i .TextMatrix(i, 1) = i + 1 .TextMatrix(i, 2) = "val" & i .TextMatrix(i, 3) = "val" & i & "= " & i + 1 Next i End With Exit Sub err_h: MsgBox Err.Description, vbCritical End Sub
By now you got the columns name in place and have full 10 rows with some values
now making the FlexGrid edit use flexgrid_DblClick() and put one TextBox make it visible=false (txtEdit is mine textbox)
i will make only Columns 2 and 4 able for editing
This code below Use FlexGrid Double Click event to place the textbox we put in teh cell boundary of the editible cell.
When the textbox is in place we can input data in and change the flexgrid cell values.
tL , th, tw and tt is used as the cell boundary of the cell of the MSFlexGrid we want to edit
lRow and lCol gets the selected row and col of the MSFlexGrid. As i say i make only Cols 2 and 4 editeble so here is in place a simple if that check if lCol is 1 or 3. It is 1 and 3 becose MSFlexGrid Cols and Rows count from 0.
So after we get conformation that this is the right col we place the textbox above the cell we want to edit.
Private Sub MSFGTable_DblClick() Dim tL As Long, tH As Long, tW As Long, tT As Long 'MSFlexGrid cell boundary On Error GoTo error_h With MSFGTable lRow = .RowSel lCol = .ColSel If lCol = 1 Or lCol = 3 Then 'Check if the clolumns we want to edit are 2 and 4 tT = .CellTop tL = .CellLeft tH = .CellHeight tW = .CellWidth 'Move the text box on the cell we dbl click on txtEdit.Move tL + .Left, tT + .Top, tW + 2 * Screen.TwipsPerPixelX, tH txtEdit.Visible = True txtEdit.ZOrder 0 txtEdit.SetFocus End If End With Exit Sub error_h: MsgBox Err.Description, vbCritical End Sub
ok now the textbox show where you want to edit use keypress event of the textbox to track vbKeyReturn and textbox LostFocus event to edit the celltext of the MSFlexGrid and hide the textbox.
so in KeyPress event we select the ascii code of the keys. 13 is for enter so we track it
Becos the values i give to the cells on Column 2 can accept ony numbers and Column 4 can accept all values (numbers and text) so we put a if so we can track only the Number values for Column 2 edited cells
I dont track esc button so you can put it and on escape to hide the textbox !!!!!!!
Private Sub txtEdit_KeyPress(KeyAscii As Integer) If KeyAscii = Str(13) Then 'Enter Select Case lCol Case 1 'Making Cell2 to accept numbers only If IsNumeric(txtEdit.Text) Then Change_FGCell_Value lRow, lCol Else ' An error and exite the function awaiting right value MsgBox "Numbers only!", vbCritical Exit Sub End If Case 3 Change_FGCell_Value lRow, lCol Case Else ' An error becose is edited wrong Column MsgBox "Error invalid Cell", vbCritical End Select End If End Sub
This function belov change the value of the cell we edit.
Private Sub Change_FGCell_Value() 'Change selected value of the cell in MSFlexGrid MSFGTable.TextMatrix(lRow, lCol) = txtEdit.Text txtEdit.Text = "" txtEdit.Visible = False End Sub
Here is the Lost Focus event of the textbox. As you can see its almost the same as keypress even but dont track for a key. It select what calumn is editing and if it is 1 make sure it is only numbers the diference is here it hide the textbox. I make it for corection if user select wrong cell by any chance. the value is stored.
Private Sub txtEdit_LostFocus() Private Sub txtEdit_LostFocus() Select Case lCol Case 1 'Making cell2 to accept numbers only If IsNumeric(txtEdit.Text) Then Change_FGCell_Value lRow, lCol Else txtEdit.Visible = False ' put txtEdit.Text="" if you want your textbox text is deleted End If Case 3 Change_FGCell_Value lRow, lCol Case Else 'An error for editing wrong column MsgBox "Error invalid Cell", vbCritical End Select End Sub
ok so here is how i do it. i hope you like it . Modify it.