highlight msflexgrid row except last column

[genlight]

I have this msflexgrid which I populate from database. So when it is first loaded from database and I move to every row, my code to highlight the row I am in is OK. But when I filter the grid records only those matching my criteria typed on textbox and only the filtered records are seen, then I click the row to highlight, the highlighting takes so much time. It seems to loop all the rows even those rows that were hidden by the filtering search.

So I would like to ask for your advice to fix the hanging of highlighting of the row.

Below are the codes

' variable declaration
Dim rw As Long
Dim rrw As Long
Dim m_lRowHeight As Long ' to store the original rowheight


'to filter the grid record
Private Sub SearchFlexGrid()
    Dim lCol As Long, lRow As Long
    Dim sSearch As String
    Dim bMatch As Boolean
    
    sSearch = txtitemname1.Text
    With fgcode
        .Redraw = False ' for faster updating
        
'        search all rows
        For lRow = .FixedRows To .Rows - 1
            If Len(sSearch) = 0 Then
'                if the search text is empty then show all rows
                bMatch = True
            Else
'                initially we don't have a match for this row
                bMatch = False
                
                For lCol = 1 To 1 '.Cols - 1    'search in column 1
'                    For lCol = .FixedCols To 1 '.Cols - 1
'                    check whether the search string is in the cell
                    bMatch = (InStr(1, .TextMatrix(lRow, lCol), sSearch, vbTextCompare) > 0)
                    
                    If bMatch Then Exit For ' no need for searching any further
                Next lCol
            End If
            
'            update the RowHeight to simulate hidden rows
            If bMatch Then
                .RowHeight(lRow) = m_lRowHeight
            Else
                .RowHeight(lRow) = 0
            End If
        Next lRow
        
        .Redraw = True ' enable updating
        .Refresh
    End With
End Sub


'to highlight the row
Public Sub rowhighlight(ColorGrid As MSFlexGrid, rr As Long)
    Dim j As Long
    Dim i As Long
    
    ColorGrid.Redraw = False
    For j = 0 To 5
        If rrw > 0 Then
            ColorGrid.Col = j
            ColorGrid.Row = rrw
            ColorGrid.CellBackColor = vbWhite
        End If
    Next
    
    For j = 0 To 5
        ColorGrid.Col = j
        ColorGrid.Row = rr
        ColorGrid.CellBackColor = &H80C0FF
    Next
    ColorGrid.Redraw = True
    ColorGrid.Refresh
    
    rrw = rr
    
    ColorGrid.Col = 19
End Sub

'grid row selchange
Private Sub fgcode_SelChange()
    Call rowhighlight(fgcode, fgcode.Row)
End Sub

Hoping to fix this real soon. Thanks

[Bobbles]

Don't hide the rows
INSTEAD
Don't load the rows

If you have a Sub to AddGridData that loops through a recordset then -
- clear the grid
- Loop through the recordset, and only load the records that match your Filter criteria

[Arnoutdv]

@Bobbles The data in the flexgrid can be anything, not perse data bound.

This seems to be based on a sample I wrote some time ago.

I think your slow down is cause by the SelChange event.
If you set the .Row/.Col in code then the SelChange event is fired.

Private Sub fgcode_SelChange()
    Static bBusy As Boolean

    ' Prevent re-entry
    If bBusy Then Exit Sub

    bBusy = True
    Call rowhighlight(fgcode, fgcode.Row)
    bBusy = False
   
End Sub

Check this sample for coloring a complete given row using the .FillStyle property.

Public Sub FlexGridRowColor(FlexGrid As MSFlexGrid, ByVal lRow As Long, ByVal lColor As Long)
  Dim lPrevCol As Long, lPrevColSel As Long
  Dim lPrevRow As Long, lPrevRowSel As Long
  Dim lPrevFillStyle As Long
  
  If lRow > FlexGrid.Rows - 1 Then Exit Sub
  
  With FlexGrid
    
    ' Store the current settings
    lPrevCol = .Col
    lPrevRow = .Row
    lPrevColSel = .ColSel
    lPrevRowSel = .RowSel
    lPrevFillStyle = .FillStyle
    
    ' Change the backcolor
    .Col = .FixedCols
    .Row = lRow
    .ColSel = .Cols - 1
    .RowSel = lRow
    .FillStyle = flexFillRepeat
    .CellBackColor = lColor
    
    ' reset the settings
    .Col = lPrevCol
    .Row = lPrevRow
    .ColSel = lPrevColSel
    .RowSel = lPrevRowSel
    .FillStyle = lPrevFillStyle
  End With
End Sub

[genlight]

Arnoutdv, I tried applying the FlexGridRowColor but still, the highlighting of row is really slow.

And what I want to achieve is when I leave the row, it will return back the cellbackcolor to vbwhite and only the current row is highlighted.

[Spooman]

gen

Regarding slowness .. I've found that using the .Visible property helps.

So, maybe changing this ..

Private Sub SearchFlexGrid()
    ....
    With fgcode
        .Redraw = False ' for faster updating
        
        ...
        ...
        
        .Redraw = True ' enable updating
        .Refresh
    End With
End Sub

.. to thiis ..

Private Sub SearchFlexGrid()
    ....
    With fgcode
        .Visible = False ' for faster updating
        
        ...
        ...
        
        .Visible = True ' enable updating
        .Refresh
    End With
End Sub

.. will to the trick

Spoo

[Arnoutdv]

Arnoutdv, I tried applying the FlexGridRowColor but still, the highlighting of row is really slow.

Did you adapt the code in SelChange event?
Because I think that is causing the problem.

[genlight]

Arnoutdv, thanks, got it now. Although, it is not as fast as the original grid that is filtered, but its better.

[Arnoutdv]

I now see what you mean.
If you have all rows visible then it's just fast.
Somehow when the grid repaints it's drawing all rows with rowheight = 0 again, giving an enormous delay.

Maybe you can solve it with having 2 grids.
The original with unfiltered items and a second grid with filtered items.
If there is text to filtered then populate grid 2 with the wanted data from the source grid.
Then show this grid and hide the source grid.
When no text is entered show the source grid.

Tested code with single grid which flickers when only 5 rows of 5000 are visible.

Option Explicit

Private m_bSearch As Boolean
Private m_lRowHeight As Long ' to store the original rowheight

Private m_lPrevRow As Long

Private Sub FG_Click()
  FlexGridRowColor FG, m_lPrevRow, vbWhite
  m_lPrevRow = FG.Row
  FlexGridRowColor FG, FG.Row, RGB(192, 255, 255)
End Sub

Private Sub Form_Load()
  Dim lCol As Long, lRow As Long
  
  m_lPrevRow = -1
  
  Randomize
  
  tmrSearch.Enabled = True
  tmrSearch.Interval = 500
  
  ' Fill the grid with some data
  With FG
    .Cols = 6
    .Rows = 5000
    .AllowBigSelection = False
    .FocusRect = flexFocusNone
    .HighLight = flexHighlightNever
    For lRow = 1 To .Rows - 1
      .TextMatrix(lRow, 0) = lRow
      For lCol = 1 To .Cols - 1
        .TextMatrix(lRow, lCol) = CLng(Rnd * 5000)
      Next lCol
    Next lRow
    
    m_lRowHeight = .RowHeight(0)
  End With
End Sub

Private Sub tmrSearch_Timer()
  Static prevSearch As String
  
  If m_bSearch Then ' m_bSearch is set the txtSearch_Change() event
    ' if the searchtext hasn't been changed for some time start the search
    If prevSearch = txtSearch.Text Then
      m_bSearch = False
      SearchFlexGrid
    End If
  End If
  
  prevSearch = txtSearch.Text

End Sub

Private Sub txtSearch_Change()
  m_bSearch = True
End Sub

Private Sub SearchFlexGrid()
  Dim lCol As Long, lRow As Long
  Dim sSearch As String
  Dim bMatch As Boolean
  
  sSearch = txtSearch.Text
  
  With FG
    .Redraw = False ' for faster updating
    .Visible = False
    ' search all rows
    For lRow = .FixedRows To .Rows - 1
      If Len(sSearch) = 0 Then
        ' if the search text is empty then show all rows
        bMatch = True
      Else
        ' initially we don't have a match for this row
        bMatch = False
        For lCol = .FixedCols To .Cols - 1
          ' check whether the search string is in the cell
          bMatch = (InStr(1, .TextMatrix(lRow, lCol), sSearch, vbTextCompare) > 0)
          If bMatch Then Exit For ' no need for searching any further
        Next lCol
      End If
      ' update the RowHeight to simulate hidden rows
      If bMatch Then
        .RowHeight(lRow) = m_lRowHeight
      Else
        .RowHeight(lRow) = 0
      End If
    Next lRow
    .Visible = True
    .Redraw = True ' enable updating
  End With
End Sub

Public Sub FlexGridRowColor(FlexGrid As MSHFlexGrid, ByVal lRow As Long, ByVal lColor As Long)
  Dim lPrevCol As Long, lPrevColSel As Long
  Dim lPrevRow As Long, lPrevRowSel As Long
  Dim lPrevFillStyle As Long
  
  If lRow <= FlexGrid.FixedRows Then Exit Sub
  If lRow > FlexGrid.Rows - 1 Then Exit Sub
  
  With FlexGrid
    ' Store the current settings
    lPrevCol = .Col
    lPrevRow = .Row
    lPrevColSel = .ColSel
    lPrevRowSel = .RowSel
    lPrevFillStyle = .FillStyle
    
    .Visible = False
    .Redraw = False
    
    ' Change the backcolor
    .Col = .FixedCols
    .Row = lRow
    .ColSel = .Cols - 1
    .RowSel = lRow
    .FillStyle = flexFillRepeat
    .CellBackColor = lColor
    
    ' reset the settings
    .Col = lPrevCol
    .Row = lPrevRow
    .ColSel = lPrevColSel
    .RowSel = lPrevRowSel
    .FillStyle = lPrevFillStyle
    
    .Redraw = True
    .Visible = True
  End With
End Sub

[Arnoutdv]

Update using 2 MSHFlexGrids:

Option Explicit

Private m_bSearch As Boolean
Private m_bShowSource As Boolean
Private m_lPrevRow As Long

Private Sub FG_Click()
  Static PrevRow As Long
  FlexGridRowColor FG, PrevRow, vbWhite
  PrevRow = FG.Row
  FlexGridRowColor FG, FG.Row, RGB(192, 255, 255)
End Sub

Private Sub FGSearch_Click()
  Static PrevRow As Long
  FlexGridRowColor FGSearch, PrevRow, vbWhite
  PrevRow = FGSearch.Row
  FlexGridRowColor FGSearch, FGSearch.Row, RGB(192, 255, 255)
End Sub

Private Sub Form_Load()
  Dim lCol As Long, lRow As Long
  
  m_lPrevRow = -1
  
  Randomize
  
  tmrSearch.Enabled = True
  tmrSearch.Interval = 500
  
  ' Fill the grid with some data
  With FG
    .Cols = 6
    .Rows = 5000
    .AllowBigSelection = False
    .FocusRect = flexFocusNone
    .HighLight = flexHighlightNever
    For lRow = 1 To .Rows - 1
      .TextMatrix(lRow, 0) = lRow
      For lCol = 1 To .Cols - 1
        .TextMatrix(lRow, lCol) = CLng(Rnd * 5000)
      Next lCol
    Next lRow
    
  End With
  
  FG.Visible = True
  FGSearch.Visible = False
  FGSearch.Move FG.Left, FG.Top, FG.Width, FG.Height
  m_bShowSource = True
End Sub

Private Sub tmrSearch_Timer()
  Static prevSearch As String
  
  If m_bSearch Then ' m_bSearch is set the txtSearch_Change() event
    ' if the searchtext hasn't been changed for some time start the search
    If prevSearch = txtSearch.Text Then
      m_bSearch = False
      SearchFlexGrid
    End If
  End If
  
  prevSearch = txtSearch.Text

End Sub

Private Sub txtSearch_Change()
  m_bSearch = True
End Sub

Private Sub SearchFlexGrid()
  Dim lCol As Long, lRow As Long, lDestRow As Long
  Dim sSearch As String
  Dim bMatch As Boolean
  Dim cCopyRows As Collection
  
  sSearch = txtSearch.Text
  m_bShowSource = Len(sSearch) = 0
  
  If Not m_bShowSource Then
    With FGSearch
      .Redraw = False
      .Cols = FG.Cols
      .Rows = FG.FixedRows
    End With
    
    With FG
      ' Copy the header
      For lRow = 0 To .FixedRows - 1
        For lCol = 0 To .Cols - 1
          FGSearch.TextMatrix(lRow, lCol) = .TextMatrix(lRow, lCol)
        Next lCol
      Next lRow
      
      ' search all rows
      For lRow = .FixedRows To .Rows - 1
        ' initially we don't have a match for this row
        bMatch = False
        For lCol = .FixedCols To .Cols - 1
          ' check whether the search string is in the cell
          bMatch = (InStr(1, .TextMatrix(lRow, lCol), sSearch, vbTextCompare) > 0)
          If bMatch Then Exit For ' no need for searching any further
        Next lCol
        If bMatch Then
          ' Copy the row content to the other grid
          lDestRow = FGSearch.Rows
          FGSearch.Rows = FGSearch.Rows + 1
          For lCol = 0 To .Cols - 1
            FGSearch.TextMatrix(lDestRow, lCol) = .TextMatrix(lRow, lCol)
          Next lCol
        End If
      Next lRow
    End With
    
    FGSearch.FixedRows = FG.FixedRows
    FGSearch.Redraw = True
  End If
  
  FG.Visible = m_bShowSource
  FGSearch.Visible = Not m_bShowSource
End Sub

Public Sub FlexGridRowColor(FlexGrid As MSHFlexGrid, ByVal lRow As Long, ByVal lColor As Long)
  Dim lPrevCol As Long, lPrevColSel As Long
  Dim lPrevRow As Long, lPrevRowSel As Long
  Dim lPrevFillStyle As Long
  
  If lRow < FlexGrid.FixedRows Then Exit Sub
  If lRow > FlexGrid.Rows - 1 Then Exit Sub
  
  With FlexGrid
    ' Store the current settings
    lPrevCol = .Col
    lPrevRow = .Row
    lPrevColSel = .ColSel
    lPrevRowSel = .RowSel
    lPrevFillStyle = .FillStyle
    
    .Visible = False
    .Redraw = False
    
    ' Change the backcolor
    .Col = .FixedCols
    .Row = lRow
    .ColSel = .Cols - 1
    .RowSel = lRow
    .FillStyle = flexFillRepeat
    .CellBackColor = lColor
    
    ' reset the settings
    .Col = lPrevCol
    .Row = lPrevRow
    .ColSel = lPrevColSel
    .RowSel = lPrevRowSel
    .FillStyle = lPrevFillStyle
    
    .Redraw = True
    .Visible = True
  End With
End Sub

[genlight]

yes, Bobbles gave me idea and used two grids. its working fine with two grids but i was hoping there is a way to only use one grid.

[Arnoutdv]

Something strange happens when the MS(H)FlexGrid does a repaint with a lot of rows with a RowHeight of 0 and just a few visible rows.
Maybe setting the control to visible, then set the .Height to a small value, do the row highlighting, restore grid height, make grid visible again can help.

[Spooman]

gen

Perhaps you could post an image of what you'd like the "only 1 FG approach"
to look like .. before (with all rows) and after (matching rows highlighted)

Spoo

[Bobbles]

yes, Bobbles gave me idea and used two grids. its working fine with two grids but i was hoping there is a way to only use one grid.

If there is a way for you to have a single Sub to Load the grid data, I could probably code that Sub to filter my way so that you use a single grid.
If you attach your project, or a trimmed down substitute(or clone), I could have a bash at it.
I haven't used the flexgrid for over a decade (been using the free SGrid2 instead)

[Arnoutdv]

Instead of using 2 grids the "source" grid can easily be replaced by a matrix (string array).