How to create a Listbox with dynamic Rowsource in VBA

Question

I get an error called (Run-time error '13' ; Type mismatch). Im new to VBA so sorry if this is a stupid question.

BaseForm = my userform

Dim iRow & iCol as integer

Sub refresh_data() ' refresh the listbox data

Set ws = ThisWorkbook.Sheets("DATA")

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
iCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
 
With BaseForm

    .ListBox1.ColumnCount = iCol
    .ListBox1.ColumnHeads = True
    
    If iRow > 1 Then
    .ListBox1.RowSource = Range(Cells(1, 1), Cells(iRow, iCol))
    
    Else
    .ListBox1.RowSource = Range(Cells(1, 1), Cells(1, iCol))

    End If
End With

End Sub

Try somehting like [THIS](https://stackoverflow.com/questions/30932787/userform-listbox-rowsource-not-populating) — Siddharth Rout, Mar 30 '21 at 08:56

score 0 · Answer 1 · answered Mar 30 '21 at 09:00

When dealing with Excel rows, use Long instead of Integer
Fully qualify your range objects. You many want to read up on Why does Range work, but not Cells?
The .RowSource expects a String

Is this what you are trying? (UNTESTED)

Option Explicit

Dim iRow As Long
Dim iCol As Long

Sub refresh_data() ' refresh the listbox data
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DATA")

    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    iCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
 
    With BaseForm
        .ListBox1.ColumnCount = iCol
        .ListBox1.ColumnHeads = True
    
        If iRow > 1 Then
            .ListBox1.RowSource = "'" & ws.Name & "'!" & _
                                  ws.Range(ws.Cells(1, 1), ws.Cells(iRow, iCol)).Address
        Else
            .ListBox1.RowSource = "'" & ws.Name & "'!" & _
                                  ws.Range(ws.Cells(1, 1), ws.Cells(1, iCol)).Address
        End If
    End With
End Sub

Thank you for the explanation and the fast reply! It was very helpful — darkerzen, Mar 30 '21 at 11:23

How to create a Listbox with dynamic Rowsource in VBA

1 Answers1

Linked