Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleDisplay a form with an editable grid in Excel VBA
DescriptionThis example shows how to display a form with an editable grid in Excel VBA.
KeywordsExcel, VBA, grid, editable, FlexGrid
CategoriesOffice, Controls
 
Differences between Visual Basic forms and VBA's UserForms prevent the solution Edit data in a FlexGrid control from working. In a UserForm, the general approach is similar but the details are slightly different.

On a UserForm, a TextBox cannot sit above the FlexGrid control so you cannot easily place one over the grid to let the user edit. However, a Frame control can sit above the FlexGrid. Place a TextBox inside a Frame with no border and use that for editing.

When the user double clicks a cell or selects a cell and starts typing, the program calls subroutine GridEdit.

 
Private Sub flxGrid_DblClick()
    GridEdit Asc(" ")
End Sub

Private Sub flxGrid_KeyPress(KeyAscii As Integer)
    GridEdit KeyAscii
End Sub
 
GridEdit positions a TextBox over the selected cell and lets the user type into it.
 
Private Sub GridEdit(KeyAscii As Integer)
Const TWIPS_TO_PIX As Single = 72 / 1440

    ' End any previous edit.
    AcceptEdit

    ' Only edit if row > 0.
    If flxGrid.Row = 0 Then Exit Sub

    ' Position the TextBox over the cell.
    fraEdit.Left = flxGrid.Left + flxGrid.CellLeft * _
        TWIPS_TO_PIX
    fraEdit.Top = flxGrid.Top + flxGrid.CellTop * _
        TWIPS_TO_PIX
    fraEdit.Width = flxGrid.CellWidth * TWIPS_TO_PIX
    fraEdit.Height = flxGrid.CellHeight * TWIPS_TO_PIX
    txtEdit.Width = fraEdit.Width
    txtEdit.Height = fraEdit.Height
    fraEdit.ZOrder msoBringToFront
    fraEdit.Visible = True
    m_EditRow = flxGrid.Row
    m_EditCol = flxGrid.Col

    Select Case KeyAscii
        Case 0 To Asc(" ")
            txtEdit.Text = flxGrid.Text
            txtEdit.SelStart = Len(txtEdit.Text)
        Case Else
            txtEdit.Text = VBA.Chr$(KeyAscii)
            txtEdit.SelStart = 1
    End Select

    DoEvents
    txtEdit.SetFocus
End Sub
 
It the user presses Escape, the program hides the TextBox and discards the user's changes.

If the user presses Return, the program calls AcceptEdit to copy the changes to the FlexGrid and hide the TextBox's Frame.

If the user presses up or down arrow, the program calls AcceptEdit and moves to a new row. The program doesn't do anything special with left or right arrows in the TextBox because the TextBox already uses those to let the user move through the text.

 
Private Sub txtEdit_KeyDown(ByVal KeyCode As _
    MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyEscape
            ' Leave the text unchanged.
            fraEdit.Visible = False
            flxGrid.SetFocus

        Case vbKeyReturn
            ' Finish editing.
            AcceptEdit
            flxGrid.SetFocus

        Case vbKeyDown
            ' Move down 1 row.
            AcceptEdit
            If flxGrid.Row < flxGrid.Rows - 1 Then
                flxGrid.Row = flxGrid.Row + 1
            End If
            DoEvents
            flxGrid.SetFocus

        Case vbKeyUp
            ' Move up 1 row.
            AcceptEdit
            If flxGrid.Row > flxGrid.FixedRows Then
                flxGrid.Row = flxGrid.Row - 1
            End If
            DoEvents
            flxGrid.SetFocus
    End Select
End Sub
 
When the user clicks on another cell, the EnterCell event handler calls AcceptEdit.
 
Private Sub flxGrid_EnterCell()
    AcceptEdit
End Sub
 
Subroutine AcceptEdit copies the changes to the FlexGrid and hides the TextBox's Frame.
 
Private Sub AcceptEdit()
    If fraEdit.Visible Then
        flxGrid.TextMatrix(m_EditRow, m_EditCol) = _
            txtEdit.Text
        fraEdit.Visible = False
    End If
End Sub
 
Unfortunately a TextBox control on a UserForm doesn't provide a LostFocus event so you cannot accept any edit when the user tabs out of the control. To work around this, the program calls AcceptEdit when focus moves into any of the other controls on the form, or when the user scrolls the FlexGrid control.
 
Private Sub cmdCancel_Enter()
    AcceptEdit
End Sub

Private Sub cmdOk_Enter()
    AcceptEdit
End Sub

Private Sub flxGrid_Enter()
    AcceptEdit
End Sub

Private Sub flxGrid_Scroll()
    AcceptEdit
End Sub
 
For more information on programming the Microsoft Office applications, see my book Microsoft Office Programming: A Guide for Experienced Developers.
 
 
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated