-
May 20th, 2017, 09:04 PM
#1
Thread Starter
Addicted Member
highlight msflexgrid row except last column
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
Code:
' 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
-
May 20th, 2017, 09:50 PM
#2
Re: highlight msflexgrid row except last column
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
-
May 22nd, 2017, 03:18 AM
#3
Re: highlight msflexgrid row except last column
@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.
Code:
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.
Code:
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
-
May 22nd, 2017, 06:45 AM
#4
Thread Starter
Addicted Member
Re: highlight msflexgrid row except last column
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.
Last edited by genlight; May 22nd, 2017 at 06:51 AM.
-
May 22nd, 2017, 07:28 AM
#5
Re: highlight msflexgrid row except last column
gen
Regarding slowness .. I've found that using the .Visible property helps.
So, maybe changing this ..
Code:
Private Sub SearchFlexGrid()
....
With fgcode
.Redraw = False ' for faster updating
...
...
.Redraw = True ' enable updating
.Refresh
End With
End Sub
.. to thiis ..
Code:
Private Sub SearchFlexGrid()
....
With fgcode
.Visible = False ' for faster updating
...
...
.Visible = True ' enable updating
.Refresh
End With
End Sub
.. will to the trick
Spoo
-
May 22nd, 2017, 07:32 AM
#6
Re: highlight msflexgrid row except last column
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.
-
May 22nd, 2017, 07:58 AM
#7
Thread Starter
Addicted Member
Re: highlight msflexgrid row except last column
Arnoutdv, thanks, got it now. Although, it is not as fast as the original grid that is filtered, but its better.
-
May 22nd, 2017, 09:02 AM
#8
Re: highlight msflexgrid row except last column
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.
Code:
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
-
May 22nd, 2017, 09:24 AM
#9
Re: highlight msflexgrid row except last column
Update using 2 MSHFlexGrids:
Code:
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
-
May 22nd, 2017, 11:01 AM
#10
Thread Starter
Addicted Member
Re: highlight msflexgrid row except last column
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.
-
May 22nd, 2017, 03:20 PM
#11
Re: highlight msflexgrid row except last column
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.
-
May 22nd, 2017, 05:17 PM
#12
Re: highlight msflexgrid row except last column
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
-
May 22nd, 2017, 08:03 PM
#13
Re: highlight msflexgrid row except last column
Originally Posted by 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.
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)
-
May 23rd, 2017, 03:35 AM
#14
Re: highlight msflexgrid row except last column
Instead of using 2 grids the "source" grid can easily be replaced by a matrix (string array).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|