Trapping Amended Data within the FlexGrid

In FlexGrid Article 2, the subroutine SaveEdits was used to copy the contents of the data-entry Textbox to the FlexGrid cell. A useful technique is to use this routine to check if the Textbox contents differ from the edited cell in the Flexgrid, indicating whether or not data has been changed or newly entered. The rewritten SaveEdits subroutine below checks if the FlexGrid cell's contents have changed, and if so enables a Save CommandBox & Menu Option.

Private Sub MSFlexGrid1_LeaveCell()
    Call SaveEdits    
End Sub

Private Sub MSFlexGrid1_GotFocus()
    Call SaveEdits    
End Sub

Private Sub SaveEdits()
    If Not txtDataEntry.Visible Then Exit Sub
    'enable Save cmdButton if new data has been entered
    If txtDataEntry.Text <> MSFlexGrid1.TextMatrix(MSFlexGrid1.row, _
      MSFlexGrid1.col) Then
        cmdCommit.Enabled = True
        mnuCommit.Enabled = True    
    End If
    MSFlexGrid1.TextMatrix(MSFlexGrid1.row, MSFlexGrid1.col) = _
      txtDataEntry.Text
    txtDataEntry.Visible = False   
End Sub

Another extension of this approach is to interrogate the status of the Save Command Button when exiting the form to alert the user to unsaved data. The QueryUnload subroutine below contains the code to check if the user intends to loose newly entered data.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

    Dim intResult As Integer

    If cmdSave.Enabled = True Then

        intResult = MsgBox("You have made changes to the form" _
        & vbCrLf _
        & "Exiting the application will loose all changes. OK?", _
        vbExclamation + vbYesNo, "Cancel Changes")
    
        If intResult = vbNo Then
            Cancel = True
	End If

    End If

End Sub

Adding ComboBoxes to FlexGrids

The ability to embed dropdown boxes in FlexGrids is often required to aid data entry and limit the options available to the user. ComboBoxes also ensure data integrity by forcing the user to select a predetermined selection rather than type data into a FlexGrid cell. ComboBoxes must be added when the form is initialised as the ComboBox is resized to fit the FlexGrid. The Form_Load subroutine below initialises the FlexGrid and adds the ComboBox over a predefined cell. The ComboBox is the resized to fit the FlexGrid, however one problem with ComboBoxes is their inability to change their height, so instead we resize the height of the FlexGrid row containing the ComboBox.

Private Sub Form_Load()

    Dim lngWidth As Long
    Const SCROLL_BAR_WIDTH = 320

    cboEntry.AddItem "Pounds"
    cboEntry.AddItem "Dollars"
    cboEntry.AddItem "Francs"
    cboEntry.ListIndex = 0

    With MSFlexGrid1

        lngWidth = .Width - SCROLL_BAR_WIDTH
        .Cols = 3
        .FixedCols = 0
        .Rows = 0
        .AddItem "Date" & vbTab & _
          "Currency" & vbTab & "Amount"
        .Rows = 2
        .FixedRows = 1
        .WordWrap = True
        .RowHeight(0) = .RowHeight(0) * 1.5
        .RowHeight(1) = .RowHeight(1) * 1.4

        .ColWidth(0) = lngWidth / 3
        .ColWidth(1) = lngWidth / 3
        .ColWidth(2) = lngWidth / 3

        'position combobox
        .row = 1
        .col = 1
        cboEntry.Move .CellLeft + .Left, .CellTop + .Top, .CellWidth

    End With

End Sub

This should result in the upper FlexGrid as shown in below:

TextBox used for Data Entry with the FlexGrid

Adding PopUp Menus to FlexGrids

Including right-button mouse-click popup menus to FlexGrids can further enhance their functionality. These can be used to invoke cell, column or row orientated functions such as edit, delete, display row/column number, save, etc.
First a menu must be added to the form with its visible property set to False, then the FlexGrids MouseDown event is used to display the Menu in a popup format.

Private Sub msgReadOnly_MouseDown(Button As Integer, _
  Shift As Integer, X As Single, Y As Single)

    If Button = vbRightButton Then
        msgReadOnly.row = msgReadOnly.MouseRow
        PopupMenu mnuPopUp
    End If

End Sub

Private Sub mnuPopUp_Click()
    'add code here to add menu functionality
End Sub

The resultant menu should display as shown below:

Figure 5: PopUp Menus used with the FlexGrid

Using FlexGrids Cell Merge to Group like Rows

Finally, to add clarity to data within a FlexGrid, the use of the MergeCells property can group common data together. The MergeCells command has 4 properties, No Merging - 0, Merge Horizontal Cells Only - 1, Merge Vertical Cells Only - 2 and Merge Both - 3. Then individual Columns or Rows can be made to merge using the MergeCol or MergeRow property respectively. Using the following code will create the FlexGrid appearance as shown below:

    .MergeCells = 2
    .MergeCol(0) = True
    .MergeCol(1) = True
    .MergeCol(2) = False

Figure 6: Cell Merging within the FlexGrid

In summary the MSFlexGrid is an extremely versatile control, which intergrates well with many other objects in many environments. Therefore if your application requires a high level of customisation, and is data orientated, then instead of redesigning the app to fit the controls, invest a little time and effort with FlexGrids and can turn out a professional looking package.

Return to FlexGrid Article 1.

Top of Page

© Ken Howe 2006