vb6 mshflexgrid

ลิงค์อ้างอิง ที่น่าสนใจคือ การทำ textbox และ combo ใน cell msh
http://www.pbdr.com/vbtips/gen/Article-UsingFlexGrids(1).htm

Using the FlexGrid Control to Manage your Data. (2/3)

Populating the FlexGrid There are two ways to populate Flexgrids: through data-binding and manually. The data-bound FlexGrid is the easiest but the least flexible approach. Add a Data Control to your form, set its DataBaseName property to an appropriate database and its RecordSource property to a table or to your required SQL query. Then set the FlexGrid's DataSource to the Data Control and when the application is run, the FlexGrid will automatically format the number of columns to the number of fields in your table or returned recordset and the number of rows to the number of records. However if you wish to display data with greater control, e.g. blank columns and rows separating grouped data, text colour and cell colour dependent upon +ve or -ve values etc., then manually, unbound populating of the FlexGrid is the best option. Once you have a recordset, data can be directed at any cell within the FlexGrid, which gives the developer much greater control. The following code example takes a recordset which contains two fields one which hold share prices, shareprice, and one for share movements, movement, and colour codes grid cells depending on the status of the data: With rsExample .MoveLast 'set number of grid rows and columns 'to fit recordset MSFlexGrid1.Rows = .RecordCount MSFlexGrid1.Cols = 3 .MoveFirst Do If !movement < 0 Then 'reference cell to set cell text colour 'to Red for shareprice MSFlexGrid1.row = .AbsolutePosition + 1 MSFlexGrid1.col = 1 MSFlexGrid1.CellForeColor = vbRed MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _ !shareprice MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 2) = _ !movement ElseIf !movement = 0 Then 'reference cell to set cell background 'colour to Yellow for shareprice MSFlexGrid1.row = .AbsolutePosition + 1 MSFlexGrid1.col = 1 MSFlexGrid1.CellBackColor = vbYellow MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _ !shareprice MSFlexGrid.TextMatrix(.AbsolutePosition + 1, 2) = _ !movement Else MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 1) = _ !shareprice MSFlexGrid1.TextMatrix(.AbsolutePosition + 1, 2) = _ !movement End If .MoveNext Loop Until .EOF End With Using this approach the FlexGrid can be formatted in any fashion for any recordset. Using a Dynamic TextBox to enter Data into the FlexGrid One disadvantage of the FlexGrid is the absence of a simple method of data entry. Date-bound FlexGrids accept data keyed directly into cells, but these amendments automatically update the recordset which populated the grid and hence the database. So again, data-binding represents a lack of flexibility. By contrast, unbound FlexGrids do not directly accept data entry, but can accept data through the FlexGrid's KeyPress event or via a Textbox. The Textbox is the favoured method as it lends itself well to validation procedures and is camouflaged well against the FlexGrid. The trick is the set the TextBox's BorderStyle property to '0 -None' and reposition the TextBox over the cell to edited and resize it's width and height to match the cell width and height. This repositioning and resizing can be achieved in one line of code using the TextBoxes Move method in the FlexGrid's Click event or  KeyPress event as follows: Private Sub MSFlexGrid1_Click() With MSFlexGrid1 txtDataEntry.Text = .TextMatrix(.row, .col) txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _ .CellWidth, .CellHeight txtDataEntry.Visible = True DoEvents txtDataEntry.SetFocus End With End Sub Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer) Select Case KeyAscii Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122 txtDataEntry.Text = Chr$(KeyAscii) txtDataEntry.SelStart = 1 End Select With MSFlexGrid1 txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _ .CellWidth, .CellHeight txtDataEntry.Visible = True DoEvents txtDataEntry.SetFocus End With End Sub The FlexGrid Click event copies the contents of the selected cell to the TextBox ready for editing and snaps the TextBox invisibly to the cell. Each subsequent keypress is then handled by the TextBox KeyPress event which appends to the original cell text. The FlexGrid KeyPress event operates in the same way as the Click event except no text is copied to the TextBox and new text can then be entered. Private Sub txtDataEntry_KeyPress(KeyAscii As Integer) Select Case KeyAscii Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122 ' These are acceptable keystrokes Case Else ' These are unacceptable, and should be ignored KeyAscii = 0 End Select End Sub The TextBox's KeyPress event can be used to validate data entry as show above. Once the required text is entered into the TextBox and another cell is selected or return is hit, then the changes are copied back to the FlexGrid cell and a command button is enabled to alert the user the save the new data. 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 MSFlexGrid1.TextMatrix(MSFlexGrid1.row, MSFlexGrid1.col) = _ txtDataEntry.Text txtDataEntry.Visible = False 'enable Save cmdButton to indicate new data to be saved cmdSave.Enable = True End Sub With these six procedures above, data entry and validation will be enabled for the FlexGrid. The txtDataEntry TextBox used with the FlexGrid should appear as shown in Figure 4. Figure 4: TextBox used for Data Entry with the FlexGrid Using Arrays behind FlexGridsMost of the time, holding data in FlexGrid is more then satisfactory, but occasions arise where a temporary datastore behind the FlexGrid is required. Consider the situation where numeric values are displayed in the FlexGrid, say millions of pounds/dollars. While you would like the grid to hold these values right down to pence/cents, you only want to display say 3 most significant figures. That is, the grid show £2.02m, but holds £2,019,995.52. For convenience, when the cell is clicked to edit the value, the full value should appear, then return to 3 s.f.'s after amendment, rather like MS Excel. This is only possible with an array as an intermediate data store between the data source and the FlexGrid. The array should be declared as a datatype appropriate to that of the data intended to be stored within it and dimension to the size of the FlexGrid. Once the data is loaded into the array, the array's contents can be transferred to the FlexGrid using the Format function (or Round function in VB6) to truncate values. Likewise edited values should be retrieved from the array to the txtDataEntry TextBox in the example above, and returned to the array upon completion of editing. A function should be called to dump the array to the FlexGrid upon data retrieval or amendment. Saving the FlexGrids data should then be a data dump from the array to the database or other data source. Adding Totals to FlexGrids Arithmetic operations upon numeric data in FlexGrids can be handled in 2 ways: by directly addressing individual grid cells, shown in the code below Dim intRow As Integer Dim dblTotal As Double With MSFlexGrid1 For intRow = 1 To 5 dblTotal = dblTotal + _ Format(.TextMatrix(intRow, 1), "General Number") Next intRow .TextMatrix(7, 1) = Format(dblTotal, "£#,##0.00") End With This code sums to values in the first 5 rows of the first column in the FlexGrid and places the total in the row 7, column 1 in a monetary format. by addressing array elements, shown in the code below Dim intArrayRow As Integer Dim dblTotal As Double Dim arrGridData() as Double ReDim arrGridData(10, 10) For intArrayRow = 1 To 5 dblTotal = dblTotal + arrGridData(intArrayRow , 1) Next intArrayRow arrGridData(7, 1) = dblTotal Call subArray2FlxGrd Again this example sums the first 5 rows and writes them the the seventh row in the first column of the array. The advantage of the array is the data is held as the correct datatype and arrays are faster in terms of read/write access than FlexGrids. Remember to include a call to the procedure to transfer the array data to the FlexGrid. Trapping Restricted Cell Rows and Columns Using the code examples above for the FlexGrid events we can use addition code to restrict data entry to specific cells or regions of the FlexGrid. Here data entry is only permitted in row 6 and between columns 4 and 8. Private Sub MSFlexGrid1_Click() With MSFlexGrid1 If (.col < 4 or .col > 8) Or (.row <> 6) then Exit Sub txtDataEntry.Text = .TextMatrix(.row, .col) txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _ .CellWidth, .CellHeight txtDataEntry.Visible = True DoEvents txtDataEntry.SetFocus End With End Sub Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer) If (.col < 4 or .col > 8) Or (.row <> 6) then Exit Sub Select Case KeyAscii Case 45, 46, 47, 48 To 57, 65 To 90, 97 To 122 txtDataEntry.Text = Chr$(KeyAscii) txtDataEntry.SelStart = 1 End Select With MSFlexGrid1 txtDataEntry.Move .CellLeft + .Left, .CellTop + .Top, _ .CellWidth, .CellHeight txtDataEntry.Visible = True DoEvents txtDataEntry.SetFocus End With End Sub On a related topic of trapping keypresses, certain keypresses can be useful, such as arrow keypresses to navigate around the FlexGrid, & Return and Escape keypresses. These can be trapped in the TextBox KeyDown event (see below) which fires before the TextBox KeyPress event coded above, and processed to carried out their required action. The Return keypress commits the TextBox contents to the FlexGrid cell, whereas Escape rolls back to the original contents of the FlexGrid cell. Private Sub txtDataEntry_KeyDown(KeyCode As Integer, _ Shift As Integer) 'calls subroutine below Call EditKeyCode(MSFlexGrid1, txtDataEntry, KeyCode, Shift) End Sub Private Sub EditKeyCode(grdGrid As MSFlexGrid, _ txtEdit As TextBox, KeyCode As Integer, Shift As Integer) Select Case KeyCode Case 27 'ESC txtEdit = "" txtEdit.Visible = False grdGrid.SetFocus Case 13 'Return grdGrid.SetFocus Case 37 'Left Arrow grdGrid.SetFocus DoEvents If grdGrid.col > grdGrid.FixedCols Then grdGrid.col = grdGrid.col - 1 End If Case 38 'Up Arrow grdGrid.SetFocus DoEvents If grdGrid.row > grdGrid.FixedRows Then grdGrid.row = grdGrid.row - 1 End If Case 39 'Right Arrow grdGrid.SetFocus DoEvents If grdGrid.col < grdGrid.Cols - 1 Then grdGrid.col = grdGrid.col + 1 End If Case 40 'Down Arrow grdGrid.SetFocus DoEvents If grdGrid.row < grdGrid.Rows - 1 Then grdGrid.row = grdGrid.row + 1 End If End Select End Sub Continue with FlexGrid Article 3.