CODEWITHSUNDEEP
vbaexcelcontrolsuserform
HeadOverFeet
HeadOverFeet

Reputation: 788

Userform listbox populate with range

Is there any way I can populate list box or other feature in user form by range of cells?

I would like to put each of my selected columns into 1 list box like in:

here.

For example A2:U100 without creating new list box for every column?

Right now I do it like:

ListBox1.List = Application.Worksheets("Můj_Ranking").Range("B2:B" & lastRw).Value
ListBox2.List = Application.Worksheets("Můj_Ranking").Range("C2:C" & lastRw).Value
ListBox3.List = Application.Worksheets("Můj_Ranking").Range("D2:D" & lastRw).Value
ListBox4.List = Application.Worksheets("Můj_Ranking").Range("E2:E" & lastRw).Value
ListBox5.List = Application.Worksheets("Můj_Ranking").Range("F2:F" & lastRw).Value
ListBox6.List = Application.Worksheets("Můj_Ranking").Range("G2:G" & lastRw).Value
ListBox7.List = Application.Worksheets("Můj_Ranking").Range("H2:H" & lastRw).Value
ListBox8.List = Application.Worksheets("Můj_Ranking").Range("I2:I" & lastRw).Value
ListBox9.List = Application.Worksheets("Můj_Ranking").Range("J2:J" & lastRw).Value
ListBox10.List = Application.Worksheets("Můj_Ranking").Range("K2:K" & lastRw).Value
ListBox11.List = Application.Worksheets("Můj_Ranking").Range("L2:L" & lastRw).Value

Upvotes: 1

Views: 28965

Answers (3)

R3uK
R3uK

Reputation: 14537

So you indeed want a ListBox with multiple columns, something like this should help :

With ListBox1
    .ColumnCount = 11
    .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50"
    .ColumnHeads = False
    .RowSource = "=Můj_Ranking!B2:L" & LastRw
    .MultiSelect = fmMultiSelectMulti
End With

Or how to loop through controls :

For i = 1 To 11
    With Application.Worksheets("Můj_Ranking")
         Controls("ListBox" & i).List = .Range(ColLet(i) & "2:" & ColLet(i) & lastRw).Value
    End With
Next i

And you also have .RowSource property for most of the controls! ;)

And the function to get the letters for columns :

Public Function ColLet(x As Integer) As String
With ActiveSheet.Columns(x)
    ColLet = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
End With
End Function

Upvotes: 1

Vulthil
Vulthil

Reputation: 768

Assuming I'm reading your question right, this should add a single line with the number of columns you have in a single ListBox. 

for i = 2 to lstRw
    With ListBox1
        .AddItem Application.Worksheets("Můj_Ranking").Range("B" & i).value
        .List(.ListCount - 1 ,1) = Application.Worksheets("Můj_Ranking").Range("C" & i).Value
        .List(.ListCount - 1 ,2) = Application.Worksheets("Můj_Ranking").Range("D" & i).Value

        'And so on for each column
        .List(.ListCount - 1 ,10) = Application.Worksheets("Můj_Ranking").Range("L" & i).Value
    End With
next i

Remember to edit the column count properties of the ListBox control on your userform of it won't work :)

Upvotes: 0

cboden
cboden

Reputation: 823

haven't tested for Listbox but here is how I fill a Combobox with the result of a recordset

 Function Fill_Combobox(ByRef cbo As ComboBox, ByVal rs As ADODB.Recordset, ByVal colWidth As String)
    Dim aryColumnWidth() As String
    Dim i As Integer

    aryColumnWidth = Split(colWidth, ";")
    cbo.Clear
    cbo.ColumnCount = UBound(aryColumnWidth) + 1
    cbo.ColumnHeads = False
    cbo.ColumnWidths = colWidth

    Do Until rs.EOF
        With cbo
            .AddItem
            For i = 0 To UBound(aryColumnWidth)
                .List(.ListCount - 1, i) = rs.Fields(i)
            Next
        End With
        rs.MoveNext
    Loop
End Function

it should work similar for a listbox. Call the AddItem Method to add an new entry to the listbox and then fill it by accessing the List element

Upvotes: 0

Related Questions