Editing Cells
Lets the user, or code, change the text of a cell in an edit box that the grid places over the cell, and reports each step of the edit in events.
The edit box is a standard Windows edit control. It takes the cell’s font, text colour, background colour and alignment, covers the cell inside its grid lines, and is a child window of the grid (hWndEdit is its handle). It exists only while a cell is being edited. A merged range is edited as one cell: the box covers the whole range, and the new text is stored in every cell of it.

Private Sub Form_Load()
VBFlexGrid1.AllowUserEditing = True
End Sub
Private Sub VBFlexGrid1_BeforeEdit(Row As Long, Col As Long, ByVal Reason As FlexEditReasonConstants, Cancel As Boolean)
If Col = 0 Then Cancel = True ' column 0 cannot be edited
End Sub
Private Sub VBFlexGrid1_AfterEdit(ByVal Row As Long, ByVal Col As Long, ByVal Changed As Boolean)
If Changed Then Debug.Print "Cell " & Row & "," & Col & " is now " & VBFlexGrid1.TextMatrix(Row, Col)
End Sub
- Starting an edit
- The life of an edit
- Refusing, moving and changing an edit
- Keys in the edit box
- Properties
- Methods
- Events
- See Also
Starting an edit
The user starts an edit only when AllowUserEditing is True (the default is False). Code starts one with StartEdit, whatever AllowUserEditing says. Every start reports its cause in EditReason and in the Reason argument of BeforeEdit, a FlexEditReasonConstants value:
| The user | Reason | What happens |
|---|---|---|
| presses F2 | FlexEditReasonF2 | The box opens with all the text selected. |
| presses Space | FlexEditReasonSpace | The box opens with the caret at the end of the text. On a cell with a check box, Space toggles the check box instead. |
| presses Backspace | FlexEditReasonBackSpace | The box opens with the text removed. |
| presses Return | FlexEditReasonReturn | The box opens with the caret at the end of the text, only when DirectionAfterReturn is FlexDirectionAfterReturnEdit. |
| types a printable character | FlexEditReasonKeyPress | The box opens and receives the character. Not while AllowIncrementalSearch is True: the character then starts a search. |
| double-clicks a cell with the left button | FlexEditReasonDblClick | Not on a fixed cell. |
| clicks a combo cue, or presses F4 or Alt+Up, Alt+Down on a cell that has one | FlexEditReasonComboCueClick, FlexEditReasonComboCueDblClick, FlexEditReasonComboCueF4, FlexEditReasonComboCueAltUpDown | See Drop-Down Lists, Buttons and Calendars. |
Space, Backspace and Return start an edit only when no modifier key is held. Code starts an edit with the reason FlexEditReasonCode.
The edit is for the current cell. With SelectionMode by row or by column, a double-click or a click on a combo cue edits the cell where the clicked row or column meets the current column or row. StartEdit takes the cell as arguments. The text in the box is the cell’s text, with one exception: a column with a ColLookup shows the looked-up description, and stores the key back.
Only one cell is edited at a time. When an edit does not start, because BeforeEdit refused it, the grid is disabled or an edit is open, StartEdit returns False.
The life of an edit
These events occur, in this order, when an edit starts:
- BeforeEdit: the edit can be refused, or moved to another cell.
- EditSetupStyle: the window styles of the edit box can be changed.
- The edit box is created.
- EditSetupWindow: the colours of the box can be changed, and the box and its combo parts can be set up.
- The box is shown. EnterEdit is the last event of the start.
While the box exists, EditKeyDown, EditKeyPress, EditKeyUp and EditChange report the typing, and EditContextMenu a right click.
These occur, in this order, when the edit ends:
- EditQueryClose: the end can be refused. It does not occur when the end is caused by the box losing the focus.
- ValidateEdit: the new text can be refused. It occurs only when the text is to be kept and has changed.
- The new text is written to the cell.
- LeaveEdit, with the box still there.
- The box is destroyed. AfterEdit is the last event.
The samples log these events with a small class, EditWatcher, that attaches to the grid. Its handlers do nothing else, apart from three switches that the later samples use.
Class EditWatcher
Private WithEvents Grid As VBFLXGRD18.VBFlexGrid
Private mLog As String
Public RefuseCol As Long = -1 ' BeforeEdit refuses an edit in this column
Public RejectText As String ' ValidateEdit refuses this text
Public KeepOpen As Boolean ' EditQueryClose refuses to close the edit
Public Sub Attach(ByVal G As VBFLXGRD18.VBFlexGrid)
Set Grid = G
End Sub
' Returns the events logged so far, and clears the log.
Public Function Take() As String
Take = Trim$(mLog)
mLog = ""
End Function
Private Sub Grid_BeforeEdit(ByRef Row As Long, ByRef Col As Long, ByVal Reason As FlexEditReasonConstants, ByRef Cancel As Boolean)
mLog &= " BeforeEdit"
If Col = RefuseCol Then Cancel = True
End Sub
Private Sub Grid_EditSetupStyle(ByRef dwStyle As Long, ByRef dwExStyle As Long)
mLog &= " EditSetupStyle"
End Sub
Private Sub Grid_EditSetupWindow(ByRef BackColor As OLE_COLOR, ByRef ForeColor As OLE_COLOR)
mLog &= " EditSetupWindow"
End Sub
Private Sub Grid_EnterEdit()
mLog &= " EnterEdit"
End Sub
Private Sub Grid_EditChange()
mLog &= " EditChange"
End Sub
Private Sub Grid_EditQueryClose(ByVal CloseMode As FlexEditCloseModeConstants, ByRef Cancel As Boolean)
mLog &= " EditQueryClose"
If KeepOpen Then Cancel = True
End Sub
Private Sub Grid_ValidateEdit(ByRef Cancel As Boolean)
mLog &= " ValidateEdit"
If Grid.EditText = RejectText Then Cancel = True
End Sub
Private Sub Grid_LeaveEdit()
mLog &= " LeaveEdit"
End Sub
Private Sub Grid_AfterEdit(ByVal Row As Long, ByVal Col As Long, ByVal Changed As Boolean)
mLog &= " AfterEdit(" & Changed & ")"
End Sub
End Class
A grid that is not shown has no window for the edit box, so the samples that run an edit show the form first.
Form1.Show
DoEvents
Dim Watch As New EditWatcher
Watch.Attach VBFlexGrid1
With VBFlexGrid1
.TextMatrix(1, 1) = "old"
.Row = 1
.Col = 1
Debug.Print .StartEdit() ' True
Debug.Print Watch.Take() ' BeforeEdit EditSetupStyle EditSetupWindow EnterEdit
.EditText = "new"
Debug.Print Watch.Take() ' EditChange
Debug.Print .CommitEdit() ' True
Debug.Print Watch.Take() ' EditQueryClose ValidateEdit LeaveEdit AfterEdit(True)
Debug.Print .TextMatrix(1, 1) ' new
End With
Ending the edit
The user ends an edit with these keys, and EditCloseMode tells which one, a FlexEditCloseModeConstants value:
| Cause | CloseMode | The text |
|---|---|---|
| Esc | FlexEditCloseModeEscape | Discarded. |
| Return | FlexEditCloseModeReturn | Kept. The key is then passed on to the grid, which moves the current cell as DirectionAfterReturn says. In a multi-line box, Shift+Return and Ctrl+Return put a carriage return and a line feed into the text instead. |
| Tab, Shift+Tab | FlexEditCloseModeTab, FlexEditCloseModeShiftTab | Kept, when TabBehavior is not FlexTabControls. The key is passed on to the grid. |
| an arrow key, Page Up, Page Down, Home or End | FlexEditCloseModeNavigationKey | Kept, when no text is selected and the caret is already at the edge that the key points to (the first line for Up, the end of the text for Right). The key is passed on to the grid. |
| the box loses the focus | FlexEditCloseModeLostFocus | Kept. EditQueryClose is not raised, and the box stays open while ValidateEdit runs. |
| code | FlexEditCloseModeCode | CommitEdit keeps the text, CancelEdit discards it. |
A kept text is written to the cell only when it has changed: a box in which nothing was typed raises no ValidateEdit and leaves the cell as it was. The Changed argument of AfterEdit is True when the text was changed at any time during the edit, even if the final text equals the old one.
When UndoLimit is above 0, a kept change is put in the undo queue.
CommitEdit and CancelEdit return True when the edit ended. They return False when no edit is open, and when EditQueryClose or ValidateEdit refused the end: the edit then stays open. After CancelEdit the cell has its old text.
Form1.Show
DoEvents
Dim Watch As New EditWatcher
Watch.Attach VBFlexGrid1
With VBFlexGrid1
.TextMatrix(1, 1) = "old"
.Row = 1
.Col = 1
.StartEdit
Debug.Print .EditCloseMode ' -1
Debug.Print .CancelEdit() ' True
Debug.Print .EditCloseMode ' 0
Debug.Print .TextMatrix(1, 1) ' old
Debug.Print .CancelEdit() ' False
.StartEdit
.EditText = "new"
Watch.Take
Watch.RejectText = "new"
Debug.Print .CommitEdit() ' False
Debug.Print Watch.Take() ' EditQueryClose ValidateEdit
Debug.Print .EditRow ' 1
Watch.RejectText = ""
Watch.KeepOpen = True
Debug.Print .CommitEdit() ' False
Debug.Print .CancelEdit() ' False
Debug.Print Watch.Take() ' EditQueryClose EditQueryClose
Watch.KeepOpen = False
Debug.Print .CommitEdit() ' True
Debug.Print Watch.Take() ' EditQueryClose ValidateEdit LeaveEdit AfterEdit(True)
Debug.Print .TextMatrix(1, 1) ' new
End With
Setting EditText from code also raises EditChange, when the text differs from the text in the box.
Refusing, moving and changing an edit
BeforeEdit takes Row and Col by reference. Setting Cancel refuses the edit: StartEdit then returns False, and EditReason is -1. Changing Row or Col edits another cell.
Form1.Show
DoEvents
Dim Watch As New EditWatcher
Watch.Attach VBFlexGrid1
With VBFlexGrid1
.Cols = 4
.Row = 1
.Col = 1
Watch.RefuseCol = 1
Debug.Print .StartEdit() ' False
Debug.Print .EditReason ' -1
Debug.Print .EditRow ' -1
Watch.RefuseCol = -1
Debug.Print .StartEdit(1, 2) ' True
Debug.Print .EditRow & "," & .EditCol ' 1,2
.CancelEdit
End With
Code can edit a cell of a fixed row or column (StartEdit 0, 0). The user can start an edit in a fixed cell with the keys, but not with a double-click.
While the box exists, code can change it. These properties raise error 5 (Invalid procedure call or argument) when no edit is open: EditText, EditLocked, EditMaxLength, EditSelStart, EditSelLength and EditSelText. The EditSetupWindow event is the place for setting them for every edit.
Form1.Show
DoEvents
With VBFlexGrid1
.TextMatrix(1, 1) = "two"
.Row = 1
.Col = 1
.StartEdit
Debug.Print .EditSelStart & " " & .EditSelLength & " " & .EditSelText ' 0 3 two
.EditSelStart = 1
.EditSelLength = 1
Debug.Print .EditSelText ' w
.EditSelText = "WW"
Debug.Print .EditText ' tWWo
.EditMaxLength = 3
.EditText = "abcdef"
Debug.Print .EditText ' abc
.CancelEdit
On Error Resume Next
Debug.Print .EditText
Debug.Print Err.Number ' 5
End With
A column with a lookup shows the description in the box and stores the key:
Form1.Show
DoEvents
With VBFlexGrid1
.ColLookup(1) = "1;One|2;Two"
.TextMatrix(1, 1) = "2"
.Row = 1
.Col = 1
.StartEdit
Debug.Print .EditText ' Two
.EditText = "One"
.CommitEdit
Debug.Print .TextMatrix(1, 1) ' 1
Debug.Print .Cell(FlexCellTextDisplay, 1, 1) ' One
End With
The next two samples change how the box looks. The styles that EditSetupStyle passes are the ES_ styles of a Windows edit control. EditSetupWindow sets the colours.
Private Sub VBFlexGrid1_EditSetupStyle(dwStyle As Long, dwExStyle As Long)
Const ES_NUMBER As Long = &H2000
If VBFlexGrid1.Col = 2 Then dwStyle = dwStyle Or ES_NUMBER ' digits only
End Sub
Private Sub VBFlexGrid1_EditSetupWindow(BackColor As OLE_COLOR, ForeColor As OLE_COLOR)
BackColor = vbInfoBackground
ForeColor = vbInfoText
End Sub
Keys in the edit box
The grid passes the keys that reach the edit box to its own events. A handler of EditKeyDown or EditKeyPress can change the key, or discard it by setting it to 0.
Private Sub VBFlexGrid1_EditKeyPress(KeyChar As Integer)
If KeyChar >= 48 And KeyChar <= 57 Then Exit Sub ' digits are accepted
If KeyChar >= 32 Then KeyChar = 0 ' other printable characters are discarded
End Sub
The window of the grid handles the keys that start an edit. The next sample calls SendKey, a procedure that sends a key-down message to that window, in place of the keyboard, so that the table of causes above can be checked: without AllowUserEditing, F2 starts nothing.
Form1.Show
DoEvents
With VBFlexGrid1
.TextMatrix(1, 1) = "abc"
.Row = 1
.Col = 1
.SetFocus
SendKey VBFlexGrid1, vbKeyF2
Debug.Print .EditRow ' -1
.AllowUserEditing = True
SendKey VBFlexGrid1, vbKeyF2
Debug.Print .EditRow & " " & .EditReason & " " & .EditSelLength ' 1 1 3
.CancelEdit
SendKey VBFlexGrid1, vbKeySpace
Debug.Print .EditReason & " " & .EditSelStart ' 2 3
.CancelEdit
SendKey VBFlexGrid1, vbKeyBack
Debug.Print .EditReason & " [" & .EditText & "]" ' 5 []
.CancelEdit
SendKey VBFlexGrid1, vbKeyReturn
Debug.Print .EditRow ' -1
.DirectionAfterReturn = FlexDirectionAfterReturnEdit
SendKey VBFlexGrid1, vbKeyReturn
Debug.Print .EditReason & " " & .EditSelStart ' 6 3
.CancelEdit
End With
Properties
AllowUserEditing
Whether the user can start an edit. Boolean. Default False.
Syntax: object.AllowUserEditing [ = value ]
True also lets a column or a cell show its combo cue. StartEdit works whatever the value is.
EditCloseMode
How the last edit ended. A FlexEditCloseModeConstants value, read-only. It is -1 while an edit is open. After an edit it keeps the value of that edit until the next edit starts.
Syntax: object.EditCloseMode
EditCol
The column of the cell being edited. Long, read-only. -1 when no edit is open.
Syntax: object.EditCol
EditLocked
Whether the box rejects the typing of the user. Boolean. Code can still change the text with EditText and the other properties. Raises error 5 when no edit is open. A column in the drop-down list combo mode has the box locked from the start.
Syntax: object.EditLocked [ = value ]
EditMaxLength
The most characters that the box takes. Long. 0 restores the default limit of a Windows edit control. A negative value raises error 380, and any use with no edit open raises error 5. Assigning a longer text to EditText cuts it to this length.
Syntax: object.EditMaxLength [ = value ]
EditReason
How the last edit started. A FlexEditReasonConstants value, read-only. It is -1 before the first edit, and after an edit that BeforeEdit refused.
Syntax: object.EditReason
EditRow
The row of the cell being edited. Long, read-only. -1 when no edit is open.
Syntax: object.EditRow
EditSelLength
The number of selected characters in the box. Long. Assigning keeps the start of the selection and sets its length. Error 380 for a negative value, error 5 with no edit open.
Syntax: object.EditSelLength [ = value ]
EditSelStart
The position of the caret, or of the start of the selection, in the box, counted from 0. Long. Assigning a position removes the selection and puts the caret there. Error 380 for a negative value, error 5 with no edit open.
Syntax: object.EditSelStart [ = value ]
EditSelText
The selected text of the box. String. Assigning replaces the selection with the text, or inserts it at the caret when nothing is selected; an empty string deletes the selection. EditChange is raised, as for typing. Error 5 with no edit open.
Syntax: object.EditSelText [ = value ]
EditText
The text in the box. String. Assigning replaces the whole text, and raises EditChange when the new text differs from the old. A text longer than EditMaxLength is cut. Error 5 with no edit open.
Syntax: object.EditText [ = value ]
hWndEdit
The window handle of the edit box. LongPtr, read-only. 0 when no edit is open.
Syntax: object.hWndEdit
Methods
CancelEdit
Ends the edit and discards the changes.
Syntax: object.CancelEdit( ) As Boolean
Raises EditQueryClose with FlexEditCloseModeCode, then LeaveEdit and AfterEdit; ValidateEdit is not raised. A drop-down list or calendar that is open is closed first. Returns True when the edit ended, False when no edit was open or EditQueryClose refused.
CommitEdit
Ends the edit and stores the text in the cell.
Syntax: object.CommitEdit( ) As Boolean
As CancelEdit, but the text is kept: when it has changed, ValidateEdit is raised, and the cell then receives the text. Returns False when no edit was open, or when EditQueryClose or ValidateEdit refused: the edit then stays open.
StartEdit
Starts editing a cell.
Syntax: object.StartEdit( [ Row ], [ Col ] ) As Boolean
- Row
- optional A Long. The row of the cell. -1, or omitted, is the current row (Row). Another value below -1 raises error 380, and a row that does not exist raises error 381 (Subscript out of range).
- Col
- optional A Long. The column of the cell, with the same rules.
Raises BeforeEdit with FlexEditReasonCode. Returns True when the box has been created. Returns False when an edit is already open, when the grid is disabled, or when BeforeEdit set Cancel.
Form1.Show
DoEvents
On Error Resume Next
With VBFlexGrid1
.StartEdit 9, 0
Debug.Print Err.Number ' 381
Err.Clear
.StartEdit -2, 0
Debug.Print Err.Number ' 380
Err.Clear
.Enabled = False
Debug.Print .StartEdit() ' False
.Enabled = True
Debug.Print .StartEdit(0, 0) ' True
Debug.Print .StartEdit() ' False
.CancelEdit
End With
Events
AfterEdit
Occurs when an edit has ended.
Syntax: object_AfterEdit( Row As Long, Col As Long, Changed As Boolean )
- Row, Col
- The cell that was edited.
- Changed
- True when the text was changed during the edit and kept, so that the cell received it. False otherwise, also after a discarded edit.
The edit box is gone, EditRow and EditCol are -1, and EditCloseMode holds the cause. The handler can start another edit.
BeforeEdit
Occurs when an edit is about to start, whatever the cause.
Syntax: object_BeforeEdit( Row As Long, Col As Long, Reason As FlexEditReasonConstants, Cancel As Boolean )
- Row, Col
- The cell. Both are passed by reference, and a handler can set them to another cell.
- Reason
- A FlexEditReasonConstants value that says what started the edit.
- Cancel
- Set to True to refuse the edit.
EditChange
Occurs when the text in the box changes, by typing, pasting, or by code (EditText, EditSelText).
Syntax: object_EditChange( )
EditContextMenu
Occurs when the user right-clicks the box, or presses Shift+F10.
Syntax: object_EditContextMenu( Handled As Boolean, X As Single, Y As Single )
- Handled
- Set to True to suppress the context menu of the box, for example to show another.
- X, Y
- The position of the click, in the units of the container. -1 and -1 when the key was used.
EditKeyDown
Occurs when a key is pressed in the box.
Syntax: object_EditKeyDown( KeyCode As Integer, Shift As Integer )
- KeyCode
- A key code, such as vbKeyF4. It is passed by reference: another value replaces the key, and 0 discards it.
- Shift
- A bit mask of vbShiftMask, vbCtrlMask and vbAltMask.
EditKeyPress
Occurs when a character key is pressed and released in the box.
Syntax: object_EditKeyPress( KeyChar As Integer )
- KeyChar
- The character code. It is passed by reference: another value replaces the character, and 0 discards it, without a beep.
EditKeyUp
Occurs when a key is released in the box.
Syntax: object_EditKeyUp( KeyCode As Integer, Shift As Integer )
The arguments are those of EditKeyDown.
EditQueryClose
Occurs when the edit is about to end, except when the box lost the focus.
Syntax: object_EditQueryClose( CloseMode As FlexEditCloseModeConstants, Cancel As Boolean )
- CloseMode
- A FlexEditCloseModeConstants value, the cause of the end.
- Cancel
- Set to True to keep the edit open. The text is neither stored nor discarded, and the key that caused the end (Return, Tab) is not passed on.
EditSetupStyle
Occurs before the edit box is created.
Syntax: object_EditSetupStyle( dwStyle As Long, dwExStyle As Long )
- dwStyle, dwExStyle
- The window style and the extended window style of the box, which a handler can change. They start with the alignment of the cell, ES_MULTILINE when WordWrap is on or SingleLine is off (else ES_AUTOHSCROLL), and the right-to-left styles. Afterwards the grid removes the border and edge styles, and sets ES_READONLY for a cell with a drop-down combo mode, or clears it for an editable one.
EditSetupWindow
Occurs after the edit box has been created and before it is shown.
Syntax: object_EditSetupWindow( BackColor As OLE_COLOR, ForeColor As OLE_COLOR )
- BackColor, ForeColor
- The colours of the box, taken from the cell. A handler can change them.
The box already holds the text of the cell, so that EditText, EditSelStart, hWndEdit and the combo properties can be used here.
EnterEdit
Occurs when the edit box has been shown.
Syntax: object_EnterEdit( )
LeaveEdit
Occurs when the edit is ending, after the text was written to the cell and before the box is destroyed.
Syntax: object_LeaveEdit( )
EditCloseMode already holds the cause.
ValidateEdit
Occurs when an edit that changed the text is about to be kept, before the cell receives the text.
Syntax: object_ValidateEdit( Cancel As Boolean )
- Cancel
- Set to True to refuse the text. The edit stays open, and the user can correct the text, which is EditText.
See Also
- Drop-Down Lists, Buttons and Calendars – the combo modes of the edit box
- Check Boxes – check boxes in cells, which need no edit
- Current Cell and Selection – DirectionAfterReturn and TabBehavior
- FlexEditReasonConstants enumeration
- FlexEditCloseModeConstants enumeration