excelvbadynamiclistboxuserform

Dynamic rowsource for listbox in excel user form


I have a question regarding listbox control in userform in ms excel.

How can I limit the search to the most recent 10 records in listbox instead of all of them?

How can this code can work for listbox rowsource?

.lstDatabase.RowSource = "Database! A & iRow-10 & :I" & iRow

Solution

  • Try this code, please. It assumes that "lstDatabase" is your list box name:

    Sub LoadLast10Rows()
     Dim sh As Worksheet, arr10 As Variant, lastRow As Long
     Set sh = ActiveSheet 'use here the sheet you need. Maybe Sheets("Database")
     lastRow = sh.Range("A" & Rows.Count).End(xlUp).row
     arr10 = sh.Range("A" & lastRow - 10 & ":A" & lastRow).Value
     Me.lstDatabase.list = arr10
    End Sub