Simple Excel/VBA database - listbox in reverse order

[stemby]

Hi,

I'm a complete newbie to VBA and have been follwing a tutorial to build a simple Excel/VBA database. I have all working great, but there is one part where I need some help if possible...

There is a form that does the data-entry and on the form it has a listbox to display the data. When data is added, it gets stored in a worksheet, and the form displays this data in a listbox.

In the code, the bit that populates the listbox is:

.lstDatabase.ColumnCount = 15
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "86,90,115,74,65,55,180,45,40,45,110,110,110,0,100"

If iRow > 1 Then

.lstDatabase.RowSource = "Database!A2:O" & iRow
Else
.lstDatabase.RowSource = "Database!A2:O2"
End If


This all works great, but when a person enters a new entry it gets stored in the next available row on the 'database' worksheet, but the listbox shows only the first xx rows that fit on the screen. Is there any way I can tweak this code so that the listbox will display the data in reverse order? So newly entered records will appear at the top of the listbox?

Many thanks
Mark

 

[6StringJazzer]

To add items in a different order than what is in the sheet range, you will have to clear the RowSource property of the control, and create a loop that uses AddItem to explicitly add the items.

There is almost enough information in your post to answer that. The missing piece is please show how you are setting iRow.

 

[stemby]

To add items in a different order than what is in the sheet range, you will have to clear the RowSource property of the control, and create a loop that uses AddItem to explicitly add the items.

There is almost enough information in your post to answer that. The missing piece is please show how you are setting iRow.

Wow, that was a quick reply...

The rowcount is set like this:

Dim iRow As Long

iRow = [counta(Database!A:A)] ' Identifying the last row


I really appreciate the help.

 

[6StringJazzer]

Can you please show all the code in this Sub? I see there must be a With statement in there. You basically want to to this but I want to confirm with your actual code.

Also I strongly suggest that you apply code tags to code so we can see the structure better. When you just paste it in, all the spacing is lost.

Dim R As Long
Dim C As Long
For R = iRow To 2 Step -1
      .lstDatabase.AddItem = Worksheets("Database").Cells(R, 1).Value
   For C = 2 To 15
      .lstDatabase.List(.lstDatabase.ListCount - 1, C - 2) = Worksheets("Database").Cells(R, C).Value 
   Next C
Next R

You must set the ColumnCount property to 15.
And remember to go into the designer and blank out the RowSource field.

I have not tested this. If you have a way to share your file I would be happy to test.

 

[stemby]

Can you please show all the code in this Sub? I see there must be a With statement in there. You basically want to to this but I want to confirm with your actual code.

Also I strongly suggest that you apply code tags to code so we can see the structure better. When you just paste it in, all the spacing is lost.

Dim R As Long
Dim C As Long
For R = iRow To 2 Step -1
      .lstDatabase.AddItem = Worksheets("Database").Cells(R, 1).Value
   For C = 2 To 15
      .lstDatabase.List(.lstDatabase.ListCount - 1, C - 2) = Worksheets("Database").Cells(R, C).Value
   Next C
Next R

You must set the ColumnCount property to 15.
And remember to go into the designer and blank out the RowSource field.

I have not tested this. If you have a way to share your file I would be happy to test.

Thanks Jeff,

I think I now have a working solution. I was doing a lot of googling yesterday and came across the bit of code for sorting shown at the bottom here:

Sub Reset()

    Dim iRow As Long
        
    iRow = [counta(Database!A:A)] ' Identifying the last row

    With frmFORM
        
    .txtBorrowerNumber.Value = ""
    .txtFirstName.Value = ""
    .txtLastName.Value = ""
    .txtDateofBirth.Value = ""
    
    .cmbInputBranch.Clear
    .cmbInputBranch.AddItem "BD"
    .cmbInputBranch.AddItem "BE"
    .cmbInputBranch.AddItem "BI"
    
    .cmbSchoolName.Clear
    shSchools.Range("A2", shSchools.Range("A" & Application.Rows.Count).End(xlUp)).Name = "Dynamic"
    .cmbSchoolName.RowSource = "Dynamic"
    .cmbSchoolName.Value = ""
            
    .chkNewMember.Value = False
    
    .optBooksRead0.Value = True
    .optBooksRead1.Value = False
    .optBooksRead2.Value = False
    .optBooksRead3.Value = False
    .optBooksRead4.Value = False
    .optBooksRead5.Value = False
    .optBooksRead6.Value = False
    
    
    .txtEntryDate.Value = ""
    .txtUsername.Value = ""
    .txtUpdDate.Value = ""
    
   'Below code is associated with Search Feature
    Call Add_SearchColumn
    ThisWorkbook.Sheets("Database").AutoFilterMode = False
    ThisWorkbook.Sheets("SearchData").AutoFilterMode = False
    ThisWorkbook.Sheets("SearchData").Cells.Clear
    
    .lstDatabase.ColumnCount = 15
    .lstDatabase.ColumnHeads = True
    .lstDatabase.ColumnWidths = "86,90,115,74,65,55,180,45,40,45,110,110,110,0,100"
    
'    If iRow > 1 Then

'        .lstDatabase.RowSource = "Database!A2:O" & iRow
        
'        Else
'        .lstDatabase.RowSource = "Database!A2:O2"
'        End If
        
        
'---
' Bit of code to sort listbox into reverse order
        
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
  
   Ary = ThisWorkbook.Sheets("Database").Range("A2:O" & iRow)
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
      Next c
   Next r
   With .lstDatabase
        .ColumnHeads = True
        .ColumnCount = 15
        .ColumnWidths = "86,90,115,74,65,55,180,45,40,45,110,110,110,0,100"
       .List = Nary
   End With
        
 '---
        
     End With
    
   
End Sub

I'm not a coder at all, and this is my first dabble with VBA so I have no idea if this is a good or elegant solution, but it seems to work OK.

I do lose the column headers this way, but I think I can just replace them with text labels.

Thanks for the help.

 

[6StringJazzer]

Sorting the worksheet is a good idea. Start with A3 instead of A2 to preserve your headers.

However, use Excel's built-in sort instead of writing your own sort code. That will be so much cleaner. Use the macro recorder to do your sorting and capture that code. Then it can be tweaked to accommodate automatically including the range as rows are added.

 

[stemby]

'---
' Bit of code to sort listbox into reverse order
       
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
 
   Ary = ThisWorkbook.Sheets("Database").Range("A2:O" & iRow)
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
      Next c
   Next r
   With .lstDatabase
        .ColumnHeads = True
        .ColumnCount = 15
        .ColumnWidths = "86,90,115,74,65,55,180,45,40,45,110,110,110,0,100"
       .List = Nary
   End With
       
 '---
       
     End With
   
End Sub

Hi, I've got this working fine to show the listbox in reverse order, but I'm just having a bit of a problem with a date format, if anyone can help?

In my worksheet (called Database) where the data is held, one the column (column D) holds dates in the format dd/mm/yyyy. However, when I use this bit of code here, the date shows as d/m/yyyy.

Does anyone know how I can tweak the above code so it will display the date as dd/mm/yyyy?

Thanks.

 

[stemby]

'---
' Bit of code to sort listbox into reverse order
     
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
 
   Ary = ThisWorkbook.Sheets("Database").Range("A2:O" & iRow)
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
      Next c
   Next r
   With .lstDatabase
        .ColumnHeads = True
        .ColumnCount = 15
        .ColumnWidths = "86,90,115,74,65,55,180,45,40,45,110,110,110,0,100"
       .List = Nary
   End With
     
 '---
     
     End With
 
End Sub

Hi, I've got this working fine to show the listbox in reverse order, but I'm just having a bit of a problem with a date format, if anyone can help?

In my worksheet (called Database) where the data is held, one the column (column D) holds dates in the format dd/mm/yyyy. However, when I use this bit of code here, the date shows as d/m/yyyy.

Does anyone know how I can tweak the above code so it will display the date as dd/mm/yyyy?

Thanks.

I think I may have found a solution. Not sure it's the best (or even correct) way but it seems to work...

' Bit of code to sort listbox into reverse order
       
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long, c As Long
 
   Ary = ThisWorkbook.Sheets("Database").Range("A2:O" & iRow)
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = UBound(Ary) To 1 Step -1
      nr = nr + 1
      For c = 1 To UBound(Ary, 2)
         Nary(nr, c) = Ary(r, c)
         Nary(nr, 4) = (Format((Nary(nr, 4)), "dd/mm/yyyy"))     '<------------------------ This line here
        
      Next c
   Next r
  
'   Nary(1, 4) = Format(Date, "dd/mm/yyyy")
  
   With .lstDatabase
        .RowSource = ""
        .ColumnHeads = False
        .ColumnCount = 11
        .ColumnWidths = "86,90,115,74,65,55,190,45,40,45,110,110,110,0,100"
        .List = Nary
       
        
   End With
       
 '---

Is there a more elegant/better way?

Thanks

 

[saesaria]

Hi..
Did you found a solution to keep the column headers on the list box after applying the reverse order code?

Thanks,
Sona SS.