something color with MSFlexGrid-VBForums
Results 1 to 38 of 38

Thread: something color with MSFlexGrid

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2004
    Posts
    294

    something color with MSFlexGrid

    can someone post some examples of something cool you can do with MSFlexGrid. im not sure what i want yet, im still brainstorming. I want something like different colors rows(all that i came up with so far). hmm that's about it, so if you have something cool looking you can do with msflexgrid, please share with me. thanks in advance

  2. #2
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid

    Don't if a cool or not but here's a quicky to do alt row colors:
    VB Code:
    1. Private Sub Command1_Click()
    2. Dim i%, j%
    3.  
    4.     With FGrid
    5.         DoEvents
    6.         If .Rows < 2 Then Exit Sub 'top row is a header
    7.         For j = 1 To .Rows - 1
    8.             For i = 1 To .Cols - 1
    9.                 .Row = j
    10.                 .Col = i
    11.                 If j Mod 2 = 0 Then
    12.                     .CellBackColor = &HDAF3CF 'light green
    13.                 Else
    14.                     .CellBackColor = vbWhite
    15.                 End If
    16.             Next i
    17.         Next j
    18.         .Row = 2
    19.         .Col = 2
    20.         .SetFocus
    21.     End With
    22.  
    23. End Sub

  3. #3
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: something color with MSFlexGrid

    Navigation and Editing
    VB Code:
    1. Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
    2.    Select Case KeyAscii
    3.         Case vbKeyReturn, vbKeyTab
    4.         'move to next cell.
    5.             With MSHFlexGrid1
    6.                 If .Col + 1 <= .Cols - 1 Then
    7.                     .Col = .Col + 1
    8.                 Else
    9.                     If .Row + 1 <= .Rows - 1 Then
    10.                         .Row = .Row + 1
    11.                         .Col = 0
    12.                     Else
    13.                         .Row = 1
    14.                         .Col = 0
    15.                     End If
    16.                 End If
    17.             End With
    18.         Case vbKeyBack
    19.             With MSHFlexGrid1
    20.                 'remove the last character, if any.
    21.                 If Len(.Text) Then
    22.                     .Text = Left(.Text, Len(.Text) - 1)
    23.                 End If
    24.             End With
    25.            
    26.         Case Is < 32
    27.        
    28.         Case Else
    29.             With MSHFlexGrid1
    30.                 .Text = .Text & Chr(KeyAscii)
    31.             End With
    32.     End Select
    33. End Sub
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  4. #4
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid

    Highlighting row with alternative (other than default) color:
    VB Code:
    1. Option Explicit
    2.  
    3. Private Const ODDROW_COLOR = &HDAF3CF
    4. Private Const EVENROW_COLOR = vbWhite
    5. Private Const SELROW_COLOR = vbYellow
    6.  
    7. Private Sub FGrid_Click()
    8. '==========================
    9. Dim j%, iCurRow%
    10. Static iLastRow As Integer
    11.  
    12.     With FGrid
    13.         If .Row = 0 Then Exit Sub
    14.         iCurRow = .Row
    15.         If .Row <> iLastRow Then
    16.             .Row = iLastRow
    17.             For j = 1 To .Cols - 1
    18.                 .Col = j
    19.                 If iLastRow Mod 2 = 0 Then
    20.                     .CellBackColor = ODDROW_COLOR
    21.                 Else
    22.                     .CellBackColor = EVENROW_COLOR
    23.                 End If
    24.             Next j
    25.         Else
    26.             Exit Sub
    27.         End If
    28.         .Row = iCurRow
    29.         For j = 1 To .Cols - 1
    30.             .Col = j
    31.             .CellBackColor = SELROW_COLOR
    32.         Next j
    33.         iLastRow = .Row
    34.     End With
    35.  
    36. End Sub

  5. #5
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Export FlexGrid To Excel

    VB Code:
    1. Dim i As Long
    2. Dim p As Long
    3. Dim newCell As String
    4. Dim xl As Excel.Application
    5. Set xl = CreateObject("excel.application")
    6.     xl.Workbooks.Open (App.Path & "\Book1")
    7.     DoEvents
    8.     xl.Visible = True
    9. For i = 1 To MSFlexGrid1.Rows - 1
    10.     For p = 1 To MSFlexGrid1.Cols - 1
    11.         MSFlexGrid1.Col = p
    12.         MSFlexGrid1.Row = i
    13.         newCell = Chr(i + 64) & p
    14.         xl.Worksheets("Sheet1").Range(newCell).Value = MSFlexGrid1.Text
    15.     Next
    16. Next
    We are on a roll RhinoBull!
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  6. #6
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid

    Oh yea, we are!!!

    Here is another one: getting selected values (this is really very quick sample):
    VB Code:
    1. Private Sub Command1_Click()
    2. '================================
    3. Dim i%, j%, intEndRow%, iEndCol%
    4. Dim sBuffer$, arValues() As String
    5.  
    6.     With FGrid
    7.         intEndRow = .RowSel
    8.         iEndCol = .ColSel
    9.         For i = intStartRow To intEndRow
    10.             sBuffer = sBuffer & .TextMatrix(i, iEndCol) & vbNewLine
    11.         Next i
    12.     End With
    13.     arValues = Split(sBuffer, vbNewLine)
    14.    
    15.     For i = 0 To UBound(arValues)
    16.         Debug.Print arValues(i)
    17.     Next i
    18.  
    19. End Sub

  7. #7
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid


    Export to a comma separated file:
    VB Code:
    1. Private Sub Command1_Click()
    2. '============================
    3. Dim i%, j%, strRowText$
    4.  
    5.     Screen.MousePointer = vbHourglass
    6.     Open App.Path & "\test.csv" For Output As #1
    7.         With FGrid
    8.             For i = 1 To .Rows - 1
    9.                 strRowText = ""
    10.                 For j = 1 To .Cols - 1
    11.                     strRowText = strRowText & .TextMatrix(i, j) & "," 'vbTab
    12.                 Next j
    13.                 Print #1, strRowText
    14.             Next i
    15.         End With
    16.     Close #1
    17.     Screen.MousePointer = vbDefault
    18.  
    19. End Sub

  8. #8
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: something color with MSFlexGrid

    Cut, Copy, Paste and Delete
    VB Code:
    1. Private Sub mnuEditCut()
    2.     'Cut the selection and put it on the Clipboard
    3.     EditCopy
    4.     EditDelete
    5. End Sub
    6.  
    7. Private Sub mnuEditCopy()
    8.     'Copy the selection and put it on the Clipboard
    9.     Clipboard.Clear
    10.     Clipboard.SetText MSFlexGrid1.Clip
    11. End Sub
    12.  
    13. Private Sub mnuEditPaste()
    14.     'Insert Clipboard contents
    15.     If Len(Clipboard.GetText) Then MSFlexGrid1.Clip = _
    16.          Clipboard.GetText
    17. End Sub
    18.  
    19. Private Sub mnuEditDelete()
    20.     'Deletes the selection
    21.     Dim i As Integer
    22.     Dim j As Integer
    23.     Dim strClip As String
    24.     With MSFlexGrid1
    25.         For i = 1 To .RowSel
    26.             For j = 1 To .ColSel
    27.                 strClip = strClip & "" & vbTab
    28.             Next
    29.             strClip = strClip & vbCr
    30.         Next
    31.         .Clip = strClip
    32.     End With
    33. End Sub
    34.  
    35. Private Sub mnuEditSelectAll()
    36.     'Selects the whole Grid
    37.     With MSFlexGrid1
    38.         .Visible = False
    39.         .row = 1
    40.         .col = 1
    41.         .RowSel = .Rows - 1
    42.         .ColSel = .Cols - 1
    43.         .TopRow = 1
    44.         .Visible = True
    45.     End With
    46. End Sub
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  9. #9

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2004
    Posts
    294

    Re: something color with MSFlexGrid

    wow, thanks alot. that really helps

  11. #11
    Lively Member meander's Avatar
    Join Date
    Jun 2004
    Posts
    121

    Re: something color with MSFlexGrid

    wow so much info need to process it all. btw i rated this as execellent since theres so much good information!
    I'm having a problem here. Do I put the serial number in the box that says 'serial number,' or do I put it in the box that says 'company'? - Oh, those poor tech support people

    Jiveman: Lookie here, I can dig grease and butter on some draggin' fruit garden.

  12. #12
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: something color with MSFlexGrid

    There are a few things you can use to optimize your FlexGrid code.

    1) Use the FillStyle property and work with a range of cells. Try to avoid looping through all rows and all columns. The code below colours alternate rows (similar to the code already posted) but executes much faster - especially with a large number of cells.

    2) Use the Redraw property - set it to False before starting the code and back to True after the code executes. This ensures the grid is not refreshed until after the process has completed.

    VB Code:
    1. Dim i As Long
    2.    
    3.     With Me.MSFlexGrid1
    4.         .Redraw = False
    5.         .FillStyle = flexFillRepeat
    6.        
    7.         'change background colour of all cells (excluding fixed cells) to white
    8.         .Row = .FixedRows
    9.         .Col = .FixedCols
    10.         .RowSel = .Rows - 1
    11.         .ColSel = .Cols - 1
    12.         .CellBackColor = vbWhite
    13.  
    14.         'change background colour of all cells in each alternate row to light green        
    15.         For i = .FixedRows + 1 To .Rows - 1 Step 2
    16.             .Row = i
    17.             .Col = .FixedCols
    18.             .ColSel = .Cols - 1
    19.             .CellBackColor = &HDAF3CF
    20.         Next
    21.        
    22.         'set back to default
    23.         .FillStyle = flexFillSingle        
    24.        
    25.          'set current cell to top left
    26.         .Row = .FixedRows
    27.         .Col = .FixedCols
    28.  
    29.         .Redraw = True
    30.     End With

    3)A quick method to clear all cells in the grid.

    VB Code:
    1. With Me.MSFlexGrid1
    2.         .Redraw = False
    3.         .FillStyle = flexFillRepeat
    4.        
    5.         'select a range of cells (in this case the entire grid).
    6.         .Row = .FixedRows
    7.         .Col = .FixedCols
    8.         .RowSel = .Rows - 1
    9.         .ColSel = .Cols - 1
    10.        
    11.         .Text = "" 'clears out data in all cells.
    12.        
    13.         .FillStyle = flexFillSingle
    14.         .Row = .FixedRows
    15.         .Col = .FixedCols
    16.  
    17.         .Redraw = True
    18.        
    19.     End With

    4) Some properties have the Row # or Column # as an argument. For example RowHeight, ColWidth, ColAlignment. If every row or every column will be set to the same value use -1 for the argument. Again, no need for looping.

    MSFlexGrid1.RowHeight(-1) = 400
    MSFlexGrid1.ColAlignment(-1) = flexAlignRightCenter

  13. #13
    Member
    Join Date
    Jul 2005
    Posts
    45

    Re: Export FlexGrid To Excel

    Hi Hack,

    I find this one interesting. Can you please help a vb6 newbie here on how to include the column headings into the excel file?

    A bunch of thanks to ya'll.

    Quote Originally Posted by Hack
    VB Code:
    1. Dim i As Long
    2. Dim p As Long
    3. Dim newCell As String
    4. Dim xl As Excel.Application
    5. Set xl = CreateObject("excel.application")
    6.     xl.Workbooks.Open (App.Path & "\Book1")
    7.     DoEvents
    8.     xl.Visible = True
    9. For i = 1 To MSFlexGrid1.Rows - 1
    10.     For p = 1 To MSFlexGrid1.Cols - 1
    11.         MSFlexGrid1.Col = p
    12.         MSFlexGrid1.Row = i
    13.         newCell = Chr(i + 64) & p
    14.         xl.Worksheets("Sheet1").Range(newCell).Value = MSFlexGrid1.Text
    15.     Next
    16. Next
    We are on a roll RhinoBull!

  14. #14
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid

    New procedure added that populates flexgrid from comma delimited file:
    VB Code:
    1. Private Sub Command1_Click()
    2. '=====================================================
    3. Dim i%, j%, strHeader$, strLine$, arItems() As String
    4.  
    5.     Screen.MousePointer = vbHourglass
    6.     Open App.Path & "\test.csv" For Input As #1
    7.         With FGrid
    8.             .Clear
    9.             .Rows = 1
    10.            
    11.             'set headers
    12.             Line Input #1, strHeader
    13.             arItems = Split(strHeader, ",")
    14.             .Cols = UBound(arItems) + 1
    15.             For j = 0 To UBound(arItems)
    16.                 .TextMatrix(i, j) = arItems(j)
    17.             Next j
    18.            
    19.             'poop through file and populate rest of a grid
    20.             i = 1
    21.             Do While Not EOF(1)
    22.                 'read line
    23.                 Line Input #1, strLine
    24.                 If Not strLine = strHeader Then
    25.                     'split values on comma
    26.                     arItems = Split(strLine, ",")
    27.                     '---
    28.                     .AddItem ""
    29.                     For j = 0 To UBound(arItems)
    30.                         .TextMatrix(i, j) = arItems(j)
    31.                     Next j
    32.                     '---
    33.                     i = i + 1
    34.                 End If
    35.             Loop
    36.         End With
    37.     Close #1
    38.     Screen.MousePointer = vbDefault
    39.  
    40. End Sub

  15. #15
    Member fishbyname's Avatar
    Join Date
    Feb 2006
    Location
    Bournemouth / Loughborough
    Posts
    43

    Re: something color with MSFlexGrid

    i've just used the export to excel code (works great) and i've modified it to include the column and row headings

    VB Code:
    1. Dim q As Long
    2. Dim p As Long
    3. Dim newCell As String
    4. Dim xl As Excel.Application
    5.  
    6. Set xl = CreateObject("excel.application")
    7.     xl.Workbooks.Open ("D:\University\3rd Year\Final Year Project\My Results\Book1")
    8.     DoEvents
    9.     xl.Visible = True
    10.  
    11. For p = 0 To MSFlexGrid1.Rows - 1
    12.     For q = 0 To MSFlexGrid1.Cols - 1
    13.         MSFlexGrid1.Col = q
    14.         MSFlexGrid1.Row = p
    15.         newCell = Chr(q + 65) & p + 1
    16.         xl.Worksheets("Sheet1").Range(newCell).Value = MSFlexGrid1.Text
    17.     Next
    18. Next

  16. #16
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: something color with MSFlexGrid

    Allows the user to rearrange the columns using Drag/Drop.

    VB Code:
    1. Private mlngDragColumn As Long
    2.  
    3. Private Sub Form_Load()
    4.     With Me.MSFlexGrid1
    5.         .Cols = 5
    6.         .TextMatrix(0, 1) = "Column 1"
    7.         .TextMatrix(0, 2) = "Column 2"
    8.         .TextMatrix(0, 3) = "Column 3"
    9.         .TextMatrix(0, 4) = "Column 4"
    10.         .DragMode = vbManual
    11.         .DragIcon = LoadPicture("C:\Program Files\Microsoft Visual Studio\Common\Graphics\Icons\Dragdrop\DROP1PG.ICO")
    12.         mlngDragColumn = -1
    13.     End With
    14. End Sub
    15.  
    16. Private Sub MSFlexGrid1_DragDrop(Source As Control, x As Single, y As Single)
    17.     If Source Is MSFlexGrid1 Then
    18.         If Source.MouseCol >= Source.FixedCols Then
    19.             If mlngDragColumn <> -1 Then
    20.                 Source.ColPosition(Source.MouseCol) = mlngDragColumn
    21.             End If
    22.         End If
    23.     End If
    24. End Sub
    25.  
    26. Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    27.     If Button = vbLeftButton Then
    28.         With MSFlexGrid1
    29.             If .MouseRow < .FixedRows And .MouseCol >= .FixedCols Then
    30.                 mlngDragColumn = .MouseCol
    31.                 .Drag vbBeginDrag
    32.             End If
    33.         End With
    34.     End If
    35. End Sub
    36.  
    37. Private Sub MSFlexGrid1_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
    38.     mlngDragColumn = -1
    39. End Sub

  17. #17
    Lively Member
    Join Date
    Jul 2006
    Location
    Brunei
    Posts
    111

    Re: something color with MSFlexGrid

    how about inserting the selected row in the msflexgrid into textbox/labelbox?

  18. #18

  19. #19
    Lively Member
    Join Date
    Jul 2006
    Location
    Brunei
    Posts
    111

    Re: something color with MSFlexGrid

    oh my goodness! i totally forgot bout that textmatrix property. lol sorry.

    edit: btw, how do i automatically refresh the flexgrid after deleting one of the records in it?

    use .refresh? it doesnt work.


    Edit Again: Sorry. found it.. it was the removeitem from rowsel.
    Last edited by Kaine; Jul 10th, 2006 at 09:09 PM.

  20. #20
    Junior Member
    Join Date
    Sep 2006
    Posts
    16

    Lightbulb Re: something color with MSFlexGrid

    Hi all,

    Found all this info overwhelming. I have been looking for this for the last 6 months.

    I have however found one anoying thing with the Drag and Drop, so have changed it a bit.

    Before It would set the column droped on to the position you dragged from, this meaning that the column being dragged only moved 1 place up or down.

    But by swapping 2 of the parameters it works as intended.

    Here is the altered code: -

    VB Code:
    1. Private mlngDragColumn As Long
    2.  
    3. Private Sub Form_Load()
    4.     With Me.MSFlexGrid1
    5.         .Cols = 5
    6.         .TextMatrix(0, 1) = "Column 1"
    7.         .TextMatrix(0, 2) = "Column 2"
    8.         .TextMatrix(0, 3) = "Column 3"
    9.         .TextMatrix(0, 4) = "Column 4"
    10.         .DragMode = vbManual
    11.         .DragIcon = LoadPicture("C:\Program Files\Microsoft Visual Studio\Common\Graphics\Icons\Dragdrop\DROP1PG.ICO")
    12.         mlngDragColumn = -1
    13.     End With
    14. End Sub
    15.  
    16. Private Sub MSFlexGrid1_DragDrop(Source As Control, x As Single, y As Single)
    17.     If Source Is MSFlexGrid1 Then
    18.         If Source.MouseCol >= Source.FixedCols Then
    19.             If mlngDragColumn <> -1 Then
    20.                 Source.ColPosition(mlngDragColumn) = Source.MouseCol ' <-- the difference is here
    21.             End If
    22.         End If
    23.     End If
    24. End Sub
    25.  
    26. Private Sub MSFlexGrid1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
    27.     If Button = vbLeftButton Then
    28.         With MSFlexGrid1
    29.             If .MouseRow < .FixedRows And .MouseCol >= .FixedCols Then
    30.                 mlngDragColumn = .MouseCol
    31.                 .Drag vbBeginDrag
    32.             End If
    33.         End With
    34.     End If
    35. End Sub
    36.  
    37. Private Sub MSFlexGrid1_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
    38.     mlngDragColumn = -1
    39. End Sub

    Hope this might help someone else.

  21. #21
    Frenzied Member zynder's Avatar
    Join Date
    Nov 2006
    Location
    localhost
    Posts
    1,434

    Re: something color with MSFlexGrid

    Fast Populate Flexgrid from Database

    VB Code:
    1. Private Sub Command1_Click()
    2.  
    3. Screen.MousePointer = vbHourglass
    4.  
    5. Set rs = New ADODB.Recordset
    6.  
    7.     strsearch = "SELECT * FROM TblName ORDER BY AnyField"
    8.     rs.Open strsearch, adoc, adOpenDynamic, adLockOptimistic
    9.     rs.Requery
    10.          
    11.    
    12.     If rs.RecordCount = 0 Then
    13.         MSFlexGrid1.Clear
    14.         MsgBox "No record found"
    15.         Screen.MousePointer = vbNormal
    16.         Exit Sub
    17.    
    18.     ElseIf rs.RecordCount >= 1 Then
    19.  
    20.         'populate flexgrid box
    21.         With MSFlexGrid1
    22.             .Clear
    23.             .Rows = rs.RecordCount + 1
    24.             .Cols = rs.Fields.Count - 1
    25.             .Row = 1
    26.             .Col = 0
    27.             .RowSel = .Rows - 1
    28.             .ColSel = .Cols - 1
    29.             .Clip = UCase(rs.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString))
    30.             .Row = 1
    31.          End With
    32.     End If
    33.    
    34.     Screen.MousePointer = vbNormal
    35. End Sub

    The GetString method returns the specified Recordset as a string so there's no loop needed.

  22. #22

  23. #23
    Junior Member
    Join Date
    Sep 2006
    Posts
    16

    Lightbulb Re: something color with MSFlexGrid

    Quote Originally Posted by zynder
    Fast Populate Flexgrid from Database

    VB Code:
    1. Private Sub Command1_Click()
    2.  
    3. Screen.MousePointer = vbHourglass
    4.  
    5. Set rs = New ADODB.Recordset
    6.  
    7.     strsearch = "SELECT * FROM TblName ORDER BY AnyField"
    8.     rs.Open strsearch, adoc, adOpenDynamic, adLockOptimistic
    9.     rs.Requery
    10.          
    11.    
    12.     If rs.RecordCount = 0 Then
    13.         MSFlexGrid1.Clear
    14.         MsgBox "No record found"
    15.         Screen.MousePointer = vbNormal
    16.         Exit Sub
    17.    
    18.     ElseIf rs.RecordCount >= 1 Then
    19.  
    20.         'populate flexgrid box
    21.         With MSFlexGrid1
    22.             .Clear
    23.             .Rows = rs.RecordCount + 1
    24.             .Cols = rs.Fields.Count - 1
    25.             .Row = 1
    26.             .Col = 0
    27.             .RowSel = .Rows - 1
    28.             .ColSel = .Cols - 1
    29.             .Clip = UCase(rs.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString))
    30.             .Row = 1
    31.          End With
    32.     End If
    33.    
    34.     Screen.MousePointer = vbNormal
    35. End Sub

    The GetString method returns the specified Recordset as a string so there's no loop needed.
    Hi Zander, only one problem with this is what if the recordset cant return a correct count, you see sometimes i get a recordset that has "-1" for the record count. So i use the following code: -

    VB Code:
    1. Public Function PopFlex(RSdb As ADODB.Recordset, Grid As MSFlexGrid, fixed As Integer) As Integer
    2.     Dim i As Integer, j As Integer, sizer() As Variant
    3.     Grid.FixedRows = 1
    4.     Grid.FixedCols = fixed
    5.     RSdb.MoveFirst
    6.     ReDim sizer(RSdb.Fields.Count)
    7.     For i = 0 To RSdb.Fields.Count - 1
    8.         sizer(i) = 0
    9.     Next
    10.     If Not RSdb.EOF Then
    11.         Grid.Rows = 1
    12.         Grid.cols = RSdb.Fields.Count
    13.         For i = 0 To RSdb.Fields.Count - 1
    14.             Grid.TextMatrix(0, i) = RSdb.Fields(i).Name
    15.             If sizer(i) < (GetTextWidth(RSdb.Fields(i).Name) + 100) Then
    16.                 sizer(i) = (GetTextWidth(RSdb.Fields(i).Name) + 100)
    17.             End If
    18.         Next
    19.        
    20.         Dim teststr As String, testarr As Variant, initval As String
    21.         initval = RSdb.GetString(adClipString, 1, Chr(9), ";", "")
    22.         Do While Not RSdb.EOF
    23.             For i = 0 To RSdb.Fields.Count - 1
    24.                 If sizer(i)<(GetTextWidth(RSdb(RSdb.Fields(i).Name)) + 100) Then
    25.                     sizer(i)=(GetTextWidth(RSdb(RSdb.Fields(i).Name)) + 100)
    26.                 End If
    27.             Next
    28.             RSdb.MoveNext
    29.         Loop
    30.         testarr = split(initval,";")
    31.         For i = 0 to UBound(testarr)
    32.             Grid.AddItem testarr(i)
    33.         Next
    34.         For i = 0 to UBound(sizer)
    35.             Grid.ColWidth(i) = sizer(i)
    36.         Next
    37.         i = Grid.Rows
    38.         Grid.RemoveItem i
    39.     End If
    40.     PopFlex = Grid.Rows - 1
    41. End Function

    ok it is not brilliant but it always works.
    also the function GetTextWidth is used to autosize the column widths.

    VB Code:
    1. Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long
    2.  
    3. Private Declare Function GetTextExtentPoint32 Lib "gdi32" Alias "GetTextExtentPoint32A" (ByVal hdc As Long, ByVal lpsz As String, ByVal cbString As Long, lpSize As SIZE) As Long
    4.  
    5. Private Declare Function GetStockObject Lib "gdi32" (ByVal nIndex As Long) As Long
    6.  
    7. Private Declare Function SelectObject Lib "gdi32" (ByVal hdc As Long, ByVal hObject As Long) As Long
    8.  
    9. Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As Long
    10.  
    11. Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hdc As Long) As Long
    12.  
    13. Public Function GetTextWidth(ByRef Frm as Form, ByVal Value As String) As Long
    14.     Dim hFont As Long
    15.     Dim hFontOrig As Long
    16.     Dim lWidth As Long
    17.     Dim hdc As Long
    18.     Dim sTemp As String
    19.     Dim sz As SIZE
    20.    
    21.     hdc = GetDC(Frm.hWnd)
    22.     hFont = GetStockObject(ANSI_VAR_FONT)
    23.     hFontOrig = SelectObject(hdc, hFont&)
    24.     GetTextExtentPoint32 hdc, Value, Len(Value), sz
    25.     lWidth = sz.cx
    26.    
    27.     SelectObject hdc, hFontOrig
    28.     DeleteObject (hFont)
    29.     ReleaseDC Frm.hWnd, hdc
    30.     GetTextWidth = lWidth * 15
    31. End Function

    This function can be used to resize any control correctly, so long as you take the padding into account for each control.

  24. #24
    Junior Member
    Join Date
    Sep 2006
    Posts
    16

    Re: something color with MSFlexGrid

    Forgot to add this Constant to my last post for the GetTextWidth Function

    Private Const ANSI_VAR_FONT As Long = 12

    Also the "If NOT RSdb.EOF then"

    Should actually be: -

    If NOT (RSdb.EOF And RSdb.BOF) then

    and if you want to trap an empty recordset and return a "No Info" message to the flexgrid, then you can put an "else" in that same if block.

  25. #25
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: something color with MSFlexGrid

    Quote Originally Posted by Izzyonstage
    Hi Zander, only one problem with this is what if the recordset cant return a correct count, you see sometimes i get a recordset that has "-1" for the record count.
    Old thread, but rs.CursorLocation = adUseClient solves that.
    The most difficult part of developing a program is understanding the problem.
    The second most difficult part is deciding how you're going to solve the problem.
    Actually writing the program (translating your solution into some computer language) is the easiest part.

    Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.

    Please Help Us To Save Ana

  26. #26
    Frenzied Member zynder's Avatar
    Join Date
    Nov 2006
    Location
    localhost
    Posts
    1,434

    Re: something color with MSFlexGrid

    Here is another fast exporting to excel without looping through cells. It's a basic copy and paste. Good for exporting thousands of records.

    Fast Export Flexgrid to Excel

    Code:
    Private Sub cmdExportToExcel_Click()
    Dim xls As Excel.Application
    
    Set xls = New Excel.Application
    
    xls.Workbooks.Open (App.Path & "\Template.xls")
    
    xls.Visible = True
    
    'select all
    With MSFlexGrid1
        .Visible = False
        .Row = 0  'will depend on the user
        .Col = 1  'will depend on the user
        .RowSel = .Rows - 1
        .ColSel = .Cols - 1
        .TopRow = 1
        .Visible = True
    End With
        Clipboard.Clear
        'copy to clipboard
        Clipboard.SetText MSFlexGrid1.Clip
        'paste to excel
        xls.ActiveSheet.Paste
        Set xls = Nothing
    End Sub
    At first I was using Hack's code, but exporting thousands of records is a bit slow, I experimented if copy paste was possible and it was.
    Last edited by zynder; Aug 25th, 2007 at 06:50 PM. Reason: added Set xls = Nothing

  27. #27
    Lively Member
    Join Date
    Apr 2009
    Posts
    94

    Cool Re: something color with MSFlexGrid

    I want to add a combo box on mshflexgrid.

    i want when the user press enter on combo box then the text in combo box
    will be with mshflexgrid for i.e.

    Code:
    Mf1.Text = cmbval.text
    Here mf1 is mshflexgrid and cmbval is combo box.

    Then combo box (cmbval) will be hide and setfocus to mshflexgrid's next column.

    I am waiting for your reply.

    Thanks.
    I belive in pray. I pray for you.
    You may have happy, healthy, wealthy life.

  28. #28

  29. #29
    New Member
    Join Date
    Jan 2008
    Location
    uk
    Posts
    14

    Re: something color with MSFlexGrid

    Rhino.

    Is there an easy way to insert and delete an entire row anywhere in a MSHFlexgrid???


    Cheers

  30. #30
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: something color with MSFlexGrid

    Use AddItem/RemoveItem:
    Code:
    Private Sub Command1_Click()
        MSFlexGrid1.AddItem "New row", 3 'or whatever rowindex you want
    End Sub
    
    Private Sub Command2_Click()
        MSFlexGrid1.RemoveItem 3 'or whatever rowindex you want
    End Sub

  31. #31
    New Member
    Join Date
    Jan 2008
    Location
    uk
    Posts
    14

    Re: something color with MSFlexGrid

    You're kidding??

    That easy..
    How did i miss that? (prob by trying to overcomplicate things as i do..)

    Thanks again.

    ..
    ..

    Rhino

    Bunged that in and works a treat.
    I am in your debt yet again
    Many thanks.
    Last edited by Steamyrotter; Jan 9th, 2011 at 05:41 PM. Reason: added a bit more..

  32. #32
    New Member
    Join Date
    Nov 2011
    Posts
    2

    Re: something color with MSFlexGrid

    hello, Have you a example to import Xml file in flexgrid?

    many thanks

  33. #33
    New Member
    Join Date
    Jan 2014
    Posts
    3

    Re: something color with MSFlexGrid

    execellent work

    can you add how to print the gird please?

  34. #34
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    992

    Re: something color with MSFlexGrid

    Have a look here and here

  35. #35
    New Member
    Join Date
    Jan 2014
    Posts
    3

    Re: something color with MSFlexGrid

    Thank you very much
    I'll check them now

    Quote Originally Posted by Arnoutdv View Post
    Have a look here and here

  36. #36
    Registered User
    Join Date
    Aug 2014
    Posts
    1

    Question Re: something color with MSFlexGrid - NEW!

    hello,

    just wan't to ask if not late..does anyone here have a code for msflexgrid(vb6) that hightlight the entire row just like ms access 2007 or 2012?..
    i mean, if i select the cell a textbox will appear then the entire row will get highlighted while the text is in focus..

    hopefully you understand what i mean..

  37. #37
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    3,731

    Re: something color with MSFlexGrid

    Here's an example....place an MSFlexgrid on the form. Make it 5 columns (0-4), and make the top row Fixed.
    Code:
    Option Explicit
    Private Sub Form_Load()
    grid.Row = 1
    Dim x As Integer
    For x = 0 To grid.Rows - 1
        grid.TextMatrix(1, x) = Str(x)
    Next x
    End Sub
    Private Sub grid_click()
    Dim a As Integer, b As Integer, rowval As Integer, i As Integer
    With grid
        For a = 0 To .Rows - 1
           .Row = a
            For b = 0 To .Cols - 1
               .Col = b
               .CellBackColor = vbWhite  'RESETS ALL ROWS/COLS to white...if your background color is not white, simply put that color here instead of vbWhite
           Next b
        Next a
    End With
    With grid
    '
    '    rowval = .MouseRow
        .Row = .MouseRow    ' the row you want to highlight
        For i = 0 To .Cols - 1
            .Col = i
            .CellBackColor = vbYellow
        Next i
    End With
    End Sub
    Also, next time, instead of resurfacing an old thread, start a new one of your own....ok?

  38. #38
    Addicted Member
    Join Date
    Jul 2012
    Location
    Tiruvallur, India
    Posts
    183

    Re: something color with MSFlexGrid

    Code:
    Dim vSql As String
    Dim vRs As New Recordset
    
    vSql = "SELECT Id,AdmNo, Name, Phone1 FROM Students ORDER BY ID"
    Set vRs = New ADODB.Recordset
    vRs.Open vSql, cnnRice, adOpenKeyset, adLockOptimistic
          
    Grid1_clear
    
    If Not vRs.EOF Then
        With MSFGrid1
            .rows = vRs.RecordCount + 1
            .Row = 1
            .Col = 1
            .RowSel = .rows - 1
            .ColSel = .cols - 1
            .clip = UCase(vRs.GetString(adClipString, -1, Chr(9), Chr(13), vbNullString))
            .Row = 1
         End With
    End If
    This is with ref. to Post #21 (Zynder). This Fast Populates the Flexgrid from Database. Is it possible to give RowNumbers (starting from 1) in the first column simultaneously ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.