CODEWITHSUNDEEP
vba
Gaus Shaikh
Gaus Shaikh

Reputation: 209

VBa to add data in listbox multiple column

I am getting an error as "Could not set list properly while filling data in list box. Please assist.

ListBox1.Clear

Dim vertical As String
lstRw = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

Dim j As Integer
j = 0

For i = 2 To lstRw
If ComboBox4.Value = ThisWorkbook.Sheets(1).Range("A" & i).Value Then

Me.ListBox1.AddItem ThisWorkbook.Sheets(1).Range("A" & i).Value

Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("B" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("C" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("D" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("E" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("F" & i).Value
Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("G" & i).Value
j = j + 1
End If

Next i

Upvotes: 0

Views: 834

Answers (1)

tlemaster
tlemaster

Reputation: 859

You need to specify the ColumnCount for your listbox. You also need to increment the variable j between each column being added. The variable j also needs to be reset to 1 as each new row is added:

Sub TestListBox()
    Dim lstRw As Integer
    Dim vertical As String
    Dim i As Integer
    Dim j As Integer
    lstRw = ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    ListBox1.ColumnCount = 7
    ListBox1.Clear
    For i = 2 To lstRw
        If ComboBox4.Value = ThisWorkbook.Sheets(1).Range("A" & i).Value Then
            Me.ListBox1.AddItem ThisWorkbook.Sheets(1).Range("A" & i).Value
            j = 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("B" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("C" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("D" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("E" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("F" & i).Value
            j = j + 1
            Me.ListBox1.List(ListBox1.ListCount - 1, j) = ThisWorkbook.Sheets(1).Range("G" & i).Value
        End If
    Next i
End Sub

Upvotes: 1

Related Questions