Excel

Reverse display of items in a listbox

Ease of Use

Intermediate

Version tested with

2003 

Submitted by:

tpoynton

Description:

This procedure takes items in a listbox and displays them in reverse order. Multicolumn listboxes are supported. 

Discussion:

By attaching this code to a checkbox or some other event, you can reverse the order items in a listbox are displayed while retaining values in all columns of a multicolumn listbox. This can be useful if, for example, you are displaying dates and want the newest date to appear at the top of the list some times, while other times you want the oldest date. The attached example contains a three column listbox with 10 rows of sample data. 

Code:

instructions for use 

			
Public Sub lBoxReverseOrder() 
     'Start copying code here
    Dim i As Long 
    Dim j As Long 
    Dim aryContents() As String 
    Dim iListCnt 
    Dim iColCount 
    With myLbox '<==set to your listbox here
         'get total # of items in listbox
        iListCnt = .ListCount - 1 
         'get the # of columns in listbox
        iColCount = .ColumnCount 
         'exit sub if there is not enough data to sort
        If .ListCount < 2 Then 
            Exit Sub 
        End If 
         'prepare array variable to hold listbox contents
        ReDim aryContents(iListCnt, iColCount) 
         'loop through each column of listbox to add items to array
        For i = 0 To iColCount - 1 
            For j = 0 To iListCnt 
                aryContents(j, i) = .List(iListCnt - j, i) 
            Next j 
        Next i 
         'set listbox contents to array
        .List = aryContents 
    End With 
     'stop copying code here
End Sub

How to use:

  1. Copy the code above where indicated
  2. Select the event you would like this to be attached to (e.g., a checkbox change event), and paste
  3. Close VBE and save, if desired
 

Test the code:

  1. Open the userform containing your listbox
  2. Take a screenshot of the active dialog by pressing Alt + PrintScreen
  3. Open a blank Word document
  4. Press CTRL + V to paste the screenshot into the Word document
  5. Trigger the event that calls the code to reverse the order of items in the listbox
  6. Compare the reversed results with the screenshot
 

Sample File:

ReverseLboxOrder.zip 14.15KB 

Approved by mdmackillop


This entry has been viewed 278 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express