CODEWITHSUNDEEP
vbadynamiclistbox
darkerzen
darkerzen

Reputation: 1

How to create a Listbox with dynamic Rowsource in VBA

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

Upvotes: 0

Views: 2561

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

  1. When dealing with Excel rows, use Long instead of Integer
  2. Fully qualify your range objects. You many want to read up on Why does Range work, but not Cells?
  3. 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

Upvotes: 0

Related Questions