Reversing rowsource order for a multicolumn listbox (1 Viewer)

[wackywoo105]

I have a 3 column list box in the format:

A1 A2 A3
B1 B2 B3
C1 C2 C3

The rowsource for this contains A1;A2;A3;B1;B2;B3;C1;C2;C3

I want to reverse the order so I have

C1 C2 C3
B1 B2 B3
A1 A2 A3

Which would give a row source of C1;C2;C3;B1;B2;B3;A1;A2;A3

Does anyone have a piece of VBA code that would manage this?

 

[TJPoorman]

Is it a value list or is the rowsource coming from a query?

 

[Galaxiom]

Me.listboxname.RowSOurce = "C1;C2;C3;B1;B2;B3;A1;A2;A3"

 

[wackywoo105]

Me.listboxname.RowSOurce = "C1;C2;C3;B1;B2;B3;A1;A2;A3"

Ok the listbox doesn’t literally contain A1 A2 A3 etc.

It contains a filename, date and description in each of the 3 columns. I do not know what it will contain until some other VBA code populates it. The number of rows also varies.

How do I reverse the rowsource property in the manor described?

 

[Galaxiom]

Since you are generating the RowSource dynamically the best option is to use a fabricated ADODB recordset as the listbox's recordset property.

This allows the recordset to easily be reversed by ordering the recordset.

 

[wackywoo105]

I have a module that populates the listbox from files contained in a specific folder. I have then written my own code that adds a date and description to each row and produces a 3 column listbox with the first column hidden.

All I have to work with is the rowsource. There must be a simple way to rebuild it in reverse but jumping 3 ";" at a time.

 

[Galaxiom]

List rowsources are not well suited to being rearranged.

You could use the Split function to create an array of the component values then build the new string by working through the array indexes in the appropriate pattern.

Personally, I would change the code to populate the listbox with the ADODB recordset.

 

[wackywoo105]

I admit it's not the most polished code but rather than completely rebuild it if I could just sort this one thing it would work perfectly.

 

[Galaxiom]

Public Function ReverseRowSource(ByRef lstbox As Access.ListBox, Optional ByVal interval As Integer = 1) As String
 
Dim ListSource As Variant
Dim ListSourceUB As Integer
Dim strTemp As String
Dim n As Integer
Dim m As Integer
Dim p As Integer
 
    ListSource = Split(lstbox.RowSource, ";")
    ListSourceUB = UBound(ListSource)
 
    p = interval - 1
 
    For n = ListSourceUB To p Step -interval
 
        For m = p To 0 Step -1
            strTemp = strTemp & ListSource(n - m) & ";"
        Next
 
    Next
 
    ReverseRowSource = Left(strTemp, Len(strTemp) - 1)
 
End Function

Use it like this:

Me.listboxname.RowSource = ReverseRowSource(Me.listboxname, 3)

 

[smig]

Why cant you reorder the list ?

 

[wackywoo105]

I had a play around with my code and figured out a way to do it by building the rowsource in reverse instead of using additem. I think I was just having a brain freeze moment about how to do it.

Call ListFiles("C:/files", , , Me.lstFileList) 'function contained in module
'MsgBox Me.lstFileList.RowSource
 Dim strTest As String
Dim strArray() As String
Dim intCount As Integer
   
strTest = Me.lstFileList.RowSource
strArray = Split(strTest, ";")
Me.lstFileList.RowSource = ""
Me.lstFileList.ColumnCount = 3
Me.lstFileList.ColumnWidths = "0cm;2.1cm"
Dim justfilename As String
Dim WrdArray() As String
   
Dim newrowsource As String
newrowsource = ""
   
For intCount = LBound(strArray) To UBound(strArray)
    'Debug.Print Trim(strArray(intCount))
    'MsgBox Right(Trim(strArray(intCount)), InStrRev(Trim(strArray(intCount)), "\"))
    If Trim(strArray(intCount)) <> Chr(34) Then
    justfilename = Right(Trim(strArray(intCount)), Len(Trim(strArray(intCount))) - InStrRev(Trim(strArray(intCount)), "\"))
    WrdArray() = Split(justfilename, "§")
    
    Dim scandate As String
    scandate = dateencrypt(Right(WrdArray(0), 2) & "/" & Mid(WrdArray(0), 5, 2) & "/" & Left(WrdArray(0), 4), False)
    'Me.lstFileList.AddItem (Trim(strArray(intCount)) & ";" & scandate & ";" & encryptstr(Left(WrdArray(2), Len(WrdArray(2)) - 5), False))
  
    If newrowsource = "" Then
        newrowsource = (Trim(strArray(intCount)) & ";" & scandate & ";" & encryptstr(Left(WrdArray(2), Len(WrdArray(2)) - 5), False))
    Else
        newrowsource = (Trim(strArray(intCount)) & ";" & scandate & ";" & encryptstr(Left(WrdArray(2), Len(WrdArray(2)) - 5), False)) & ";" & newrowsource
    End If
  
    End If
Next
Me.lstFileList.RowSource = newrowsource

That ReverseRowSource function is however much more useful as I can use it elsewhere in my code.