Edit Data in a Read Only Grid (MSFlexGrid)

Basically, a textbox is positioned over the cell that you want to edit. When you click the cell, hit the Enter key or begin typing in the selected cell, its contents are copied into the textbox allowing you to make the desired changes. When the textbox looses focus the changed data is copied back to the grid cell and the textbox is hidden. The trick is to make this look and feel as transparent as possible. The key to doing this is positioning the textbox to correctly match the cell in the grid and allowing a simple means of navigating through the grid.

The arrow keys allow you to navigate through the cells in the grid. When you click a cell or press the Enter or Tab key, the textbox is displayed so that you can make your changes.

Once in this "Edit" mode, the horizontal arrow keys can be used to move through the textbox's data.

Pressing the vertical arrow, Enter or Tab key transfers the data back to the grid and takes you out of "Edit" mode. I check to make sure that clicking on a fixed row or column in the grid does not display the textbox. Also, I make sure the grid and textbox use the same size and style of font so that the textbox aligns correctly by setting these properties manually in the form's load event. You can add additional code to the textbox to validate and format the data before writing it back to the grid and to align it left or right so that it looks the same in the textbox as it does in the grid.

here is the code :

---

Option Explicit

Dim bDoNotEdit   As Boolean
Dim bOnFixedPart As Boolean

Private Sub Form_Click()
Call pSetCellValue
End Sub

Private Sub gridTest_Click()
'
' Display the textbox if the user clicked
' on a non-fixed row or column.
'
If bOnFixedPart Then Exit Sub
Call pEditGrid(32)
End Sub
Private Sub pEditGrid(KeyAscii As Integer)
'
' Populate the textbox and position it.
'
With txtEdit
    Select Case KeyAscii
        Case 0 To 32
            '
            ' Edit the current text.
            '
            .Text = gridTest
            .SelStart = 0
            .SelLength = 1000
            
        Case 8, 46, 48 To 57
            '
            ' Replace the current text but only
            ' if the user entered a number.
            '
            .Text = Chr(KeyAscii)
            .SelStart = 1
        Case Else
            '
            ' If an alpha character was entered,
            ' use a zero instead.
            '
            .Text = "0"
    End Select
End With
'
' Show the textbox at the right place.
'
With gridTest
    If .CellWidth < 0 Then Exit Sub
    txtEdit.Move .Left + .CellLeft, .Top + .CellTop, .CellWidth, .CellHeight
    '
    ' NOTE:
    '   Depending on the style of the Grid Lines that you set, you
    '   may need to adjust the textbox position slightly. For example
    '   if you use raised grid lines use the following:
    '
    'txtEdit.Move .Left + .CellLeft, .Top + .CellTop, .CellWidth - 8, .CellHeight - 8
End With

txtEdit.Visible = True
txtEdit.SetFocus
End Sub
Private Sub gridTest_GotFocus()

If bDoNotEdit Then Exit Sub
'
' Copy the textbox's value to the grid
' and hide the textbox.
'
Call pSetCellValue
End Sub
Private Sub gridTest_KeyPress(KeyAscii As Integer)
'
' Display the textbox.
'
Call pEditGrid(KeyAscii)
End Sub
Private Sub gridTest_LeaveCell()

If bDoNotEdit Then Exit Sub
Call gridTest_GotFocus
End Sub
Private Sub gridTest_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Dim l      As Long
Dim lWidth As Long

With gridTest
    For l = 0 To .Cols - 1
        If .ColIsVisible(l) Then
            lWidth = lWidth + .ColWidth(l)
        End If
    Next
    '
    ' See if we are on the fixed part of the grid.
    '
    bOnFixedPart = (x < .ColWidth(0)) Or _
                   (x > lWidth) Or _
                   (y < .RowHeight(0)) Or _
                   (y > .Rows * .RowHeightMin)
End With
End Sub
Private Sub gridTest_Scroll()
Call gridTest_GotFocus
End Sub
Private Sub Form_Load()
Dim i As Long
'
' Set the grid and textbox
' to the same font.
'
With txtEdit.Font
    .Name = gridTest.Font.Name
    .Size = gridTest.Font.Size
    .Weight = gridTest.Font.Weight
End With
txtEdit.BackColor = vb3DLight
'
' Add some rows and columns to the grid so we
' have something to start with.
'
With gridTest
    .RowHeightMin = txtEdit.Height

    ' Size the first fixed column.
    .ColWidth(0) = .ColWidth(0) / 2
    .ColAlignment(0) = 1   ' Center center.
    
    ' Label the rows.
    For i = .FixedRows To .Rows - 1
         .TextArray(fLabel(i, 0)) = i
    Next
    
    ' Label the columns.
    For i = .FixedCols To .Cols - 1
        .TextArray(fLabel(0, i)) = i
    Next
    
    ' Right align data.
    For i = .FixedCols To .Cols - 1
        .ColAlignment(i) = flexAlignRightCenter
    Next
End With

txtEdit = ""
bDoNotEdit = False
End Sub
Private Function fLabel(lRow As Long, lCol As Long) As Long
    fLabel = lCol + gridTest.Cols * lRow
End Function
Private Sub txtEdit_KeyDown(KeyCode As Integer, Shift As Integer)
'
' See what key was pressed in the textbox.
'
With gridTest
    Select Case KeyCode
        Case 13   'ENTER
            .SetFocus
        Case 27   'ESC
             txtEdit.Visible = False
            .SetFocus
        Case 38   'Up arrow
            .SetFocus
            DoEvents
            If .Row > .FixedRows Then
                bDoNotEdit = True
                .Row = .Row - 1
                bDoNotEdit = False
            End If
        Case 40   'Down arrow
            .SetFocus
            DoEvents
            If .Row < .Rows - 1 Then
                bDoNotEdit = True
                .Row = .Row + 1
                bDoNotEdit = False
            End If
    End Select
End With
End Sub
Private Sub txtEdit_KeyPress(KeyAscii As Integer)
'
' Delete carriage returns to get rid of beep
' and only allow numbers.
'
Select Case KeyAscii
    Case Asc(vbCr)
        KeyAscii = 0
    Case 8, 46
    Case 48 To 57
    Case Else
        KeyAscii = 0
End Select
End Sub
Private Sub pSetCellValue()
'
' NOTE:
'       This code should be called anytime
'       the grid loses focus and the grid's
'       contents may change.  Otherwise, the
'       cell's new value may be lost and the
'       textbox may not line up correctly.
'
If txtEdit.Visible Then
    gridTest.Text = txtEdit.Text
    txtEdit.Visible = False
End If
End Sub

---