How to show the last 10 entries in listbox made VBA

Question

It seems that sorting in descending form in listbox VBA is a little bit hard like my question here. Is there any way I can display the last 10 entries made in listbox? I keep scrolling down to see the last 10 entries at the bottom of the listbox. Attached are the images of the code and the form. Please understand that I am using mobile to capture it because transferring data from my laptop is restricted.

Private Sub CommandButton1_Click()
 Dim Row As Long
 Row = [Counta(ExcelEntryDB!A:E)]
 Me.ListBox1.ColumnCount = 3
 Me.ListBox1.ColumnHeads = True
 Me.ListBox1.ColumnWidths = "75;75;75"

 If Row > 1 Then
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E" & Row
 Else
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E2" & Row
 End If

 Dim sh As Worksheet
 Set sh = 
 ThisWorkbook.Sheets("ExcelEntryDB")
 Dim n As Long

 n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
     sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")
   sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss" AM/PM)
   sh.Range("E" & n + 1).Value = Me.TextBox3.Value

 Me.TextBox3.Value = ""

End Sub

[SOLVED] BIG THANKS TO taller_ExcelHome

Private Sub CommandButton1_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("ExcelEntryDB")

Dim n As Long

n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row

    sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")

    sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss AM/PM")

    sh.Range("E" & n + 1).Value = Me.txtColor.Value

    sh.Range("G" & n + 1).Value = Me.txtName.Value

    sh.Range("H" & n + 1).Value = Me.txtShape.Value

Me.txtName.Value = ""

Me.txtColor.Value = ""

Me.txtShape.Value = ""

Call arrayR

End Sub

Sub arrayR()

   Dim arr(), lastRow As Long, i As Integer 'get last column number using index

    With ActiveSheet

    lastRow = .Cells(Rows.Count, 3).End(xlUp).Row

ReDim arr(IIf(lastRow > 20, 20, lastRow - 1), 5)

arr(0, 0) = .Cells(1, 3)

arr(0, 1) = .Cells(1, 4)

arr(0, 2) = .Cells(1, 5)

arr(0, 4) = .Cells(1, 7)

arr(0, 5) = .Cells(1, 8)

    If lastRow > 20 Then

        For i = 1 To 20

            arr(i, 0) = .Cells(lastRow - 20 + i, 3).Text

            arr(i, 1) = .Cells(lastRow - 20 + i, 4).Text

            arr(i, 2) = .Cells(lastRow - 20 + i, 5).Text

            arr(i, 4) = .Cells(lastRow - 20 + i, 7).Text

            arr(i, 5) = .Cells(lastRow - 20 + i, 8).Text

        Next

    Else

        For i = 1 To lastRow - 1

            arr(i, 0) = .Cells(i + 1, 3).Text

            arr(i, 1) = .Cells(i + 1, 4).Text

            arr(i, 2) = .Cells(i + 1, 5).Text

            arr(i, 4) = .Cells(i + 1, 7).Text

            arr(i, 5) = .Cells(lastRow - 20 + i, 8).Text

        Next

    End If

End With

With Me.ListBox1

.ColumnHeads = True

.ColumnCount = 6

.ColumnWidths = "75,75,75,75,75,75"

.List = arr()

End With

End Sub

Private Sub UserForm_Initialize()

arrayR

End Sub

Answer 1

Here is a solution close to your needs, with some differences:

• Listbox column headers can't set by .List property.
• .RowSource uses header row which is next to the data range (RowSource). You can't get the correct header if using last 10 rows as RowSource.
• First row in listbox becomes a "header" but is also selectable, requiring exception handling in your code

Private Sub UserForm_Initialize()
    Dim arr(10, 1), lastRow As Long, i As Integer
    With ActiveSheet
        arr(0, 0) = .Cells(1, 3)
        arr(0, 1) = .Cells(1, 4)
        lastRow = .Cells(Rows.Count, 3).End(xlUp).Row
        If lastRow > 10 Then
            For i = 1 To 10
                arr(i, 0) = .Cells(lastRow - 10 + i, 3).Text
                arr(i, 1) = .Cells(lastRow - 10 + i, 4).Text
            Next
        Else
            For i = 1 To lastRow - 1
                arr(i, 0) = .Cells(i + 1, 3).Text
                arr(i, 1) = .Cells(i + 1, 4).Text
            Next
        End If
    End With
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnWidths = "75;75"
        .List = arr()
    End With
End Sub

Updated code to load 3 columns in Listbox.

Private Sub UserForm_Initialize()
    Dim arr(), lastRow As Long, i As Integer
    With ActiveSheet
        lastRow = .Cells(Rows.Count, 3).End(xlUp).Row
        ReDim arr(IIf(lastRow > 10, 10, lastRow - 1), 2)
        arr(0, 0) = .Cells(1, 3)
        arr(0, 1) = .Cells(1, 4)
        arr(0, 2) = .Cells(1, 5)
        If lastRow > 10 Then
            For i = 1 To 10
                arr(i, 0) = .Cells(lastRow - 10 + i, 3).Text
                arr(i, 1) = .Cells(lastRow - 10 + i, 4).Text
                arr(i, 2) = .Cells(lastRow - 10 + i, 5).Text
            Next
        Else
            For i = 1 To lastRow - 1
                arr(i, 0) = .Cells(i + 1, 3).Text
                arr(i, 1) = .Cells(i + 1, 4).Text
                arr(i, 2) = .Cells(i + 1, 5).Text
            Next
        End If
    End With
    With Me.ListBox1
        .ColumnCount = 3
        .ColumnWidths = "75;75;75"
        .List = arr()
    End With
End Sub

Alternative is a Label above listbox for static header

Loading Listbox rows via .List allows flexibility like reversing order. The last row (row 14 in the first screenshot) can be shown first.

---

Spare columns (i.e column AA and AB) to store listbox data allows implementing the exactly desired listbox behavior. Spare columns could be cleaned in UserForm_Terminate() event.

Private Sub UserForm_Initialize()
    Dim lastRow As Long
    With ActiveSheet
        .Range("AA:AB").Clear
        .Range("AB:AB").NumberFormatLocal = "h:mm:ss AM/PM"
        .Range("AA:AA").NumberFormatLocal = "m/d/yyyy"
        lastRow = .Cells(Rows.Count, 3).End(xlUp).Row
        .[aa1].Resize(1, 2).Value = .Range("C1:D1").Value
        If lastRow > 10 Then '*
            .[aa2].Resize(10, 3).Value = .Range(.Cells(lastRow - 9, 3), .Cells(lastRow, 4)).Value '*
        Else '*
            .[aa2].Resize(lastRow - 1, 3).Value = .Range(.Cells(2, 3), .Cells(lastRow, 4)).Value '*
        End If '*
        lastRow = .Cells(Rows.Count, "AA").End(xlUp).Row
    End With
    With Me.ListBox1
        .ColumnCount = 2
        .ColumnWidths = "75;75"
        .ColumnHeads = True
        .RowSource = "AA2:AB" & lastRow
    End With
End Sub

---

Update

Loading three columns, updated code are mark with "'*"

Private Sub UserForm_Initialize()
    Dim lastRow As Long
    With ActiveSheet
        .Range("AA:AC").Clear '*
        .Range("AB:AB").NumberFormatLocal = "h:mm:ss AM/PM"
        .Range("AA:AA").NumberFormatLocal = "m/d/yyyy"
        lastRow = .Cells(Rows.Count, 3).End(xlUp).Row '*
        .[aa1].Resize(1, 3).Value = .Range("C1:E1").Value '*
        If lastRow > 10 Then '*
            .[aa2].Resize(10, 3).Value = .Range(.Cells(lastRow - 9, 3), .Cells(lastRow, 5)).Value '*
        Else '*
            .[aa2].Resize(lastRow - 1, 3).Value = .Range(.Cells(2, 3), .Cells(lastRow, 5)).Value '*
        End If '*
        lastRow = .Cells(Rows.Count, "AA").End(xlUp).Row
    End With
    With Me.ListBox1
        .ColumnCount = 3 '*
        .ColumnWidths = "75;75;75"  '*
        .ColumnHeads = True
        .RowSource = "AA2:AC" & lastRow  '*
    End With
End Sub