display problem with listbox

[Luke Alcatel]

I use an MSForms ListBox and I've implemented functions to sort the rows
according to column values when I click on a column header. I have found
that after sorting, the displayed rows are in correct sort order but rows
that were scrolled out of view often are not, i.e. those rows are not
repainted. I tried a repaint in the scroll event handler but this did not
correct the problem. How can I force the needed graphical rendering update?

Luke

 

[JLGWhiz]

Have you actually isolated the problem to being a graphic repaint problem and
not a problem with the function to sort the data? It is difficult to analyze
without seeing the relative code.

 

[Luke Alcatel]

When I make the ListBox unusually large such that there is no scrolling for
a typical set of data this problem does not occur. I attached the code
below although I haven't included my bubbleSort which I can provide if
anyone really wants it. Suffice to say that the same sort has been used in
other programs and even in this program there is no problem when the ListBox
is large.

Luke

'Sort the rows of ListBox lbox using the entries in column number col
'(zero-based) as a key. labels is the column headings, the label on
'which the table is sorted is colored blue, others are black. The sort
'algorithm understands lexical and non-negative numeric ordering.
Public Sub listBoxSort(lbox As MSForms.ListBox, _
labels() As MSForms.label, ByVal col As Integer)

Dim ndx As Integer, a() As String, p() As String, val As String
Dim walk As Integer, place As Integer, v() As String

If col < 0 Or col >= lbox.ColumnCount Then
Exit Sub
End If
'extract keys and table data
ReDim a(lbox.ListCount - 1)
ReDim v(lbox.ListCount - 1)
For ndx = 0 To lbox.ListCount - 1
val = lbox.list(ndx, col)
If IsNumeric(val) Then
val = Format(val, "0000000000000000")
Else
val = val & left(" ", 16 - Len(val))
End If
a(ndx) = val & vbTab & ndx
v(ndx) = lbox.list(ndx, 0)
For walk = 1 To lbox.ColumnCount - 1
v(ndx) = v(ndx) & vbTab & lbox.list(ndx, walk)
Next walk
Next ndx
'sort keys and pointers to data
bubbleSort a, lbox.ListCount, 0
're-insert sorted table data
For ndx = 0 To UBound(a)
p = Split(a(ndx), vbTab)
place = p(1)
p = Split(v(place), vbTab)
For walk = 0 To UBound(p)
lbox.list(ndx, walk) = p(walk)
Next walk
Next ndx
'highlight the label for column used as sort keys
For ndx = 0 To UBound(labels)
If ndx = col Then
labels(ndx).ForeColor = RGB(0, 0, 255)
Else
labels(ndx).ForeColor = &H80000012
End If
Next ndx
End Sub

 

[Luke Alcatel]

I have an indirect solution. Before rewriting each row of the sorted
ListBox I set the TopIndex property to that row number. This insures that
when a row is updated it is visible. There is the somewhat weird effect of
the listbox scrolling around by itself but think of it as a progress bar
. The solution demonstrates that the core issue really is repaint. I'm
using Excel 2000 so it's possible that this problem does not occur in later
versions.

Luke