[raziel_]

FlexGrid Editing by users


hello all
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.

the variables
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.