Listbox Userform delimited rowsource

[johnbird1988]

Hello

I would like some help with populating my listbox within my userform.

My source data for the useform is in a single cell. Within the cell, each column is seperated by a | and every row is a carriage return (new line)

So far I have:

With Me.ListBox1
.Clear
.ColumnCount = 6
.ColumnWidths = "50;50;50;50;50;50"
.RowSource = Split(Sheet15.Range("AR4"), "|")
End With

But I am not having any luck, any help would be great. I am sure this can be done!

Cheers,

John

 

[cdchapman]

Hello John,

I haven't tested this thoroughly, but try the following code:

Sub Populate_Listbox()


    Dim RowSplit
    Dim ColSplit
    Dim RowSrc()
    Dim i as Integer
    
    '1. Split the data into rows by the carriage return character (chr(10))
    RowSplit = Split(Worksheets("Sheet1").Range("AR4"), Chr(10))
    
    '2. Prepare the RowSrc array that is used to populate the listbox
    ReDim RowSrc(UBound(RowSplit), 5)
    
    '3. For each set of row data in RowSplit, split this further into the columns (by "|")
    '    and store in the RowSrc array
    For i = 0 To UBound(RowSrc)
        ColSplit = Split(RowSplit(i), "|")
        RowSrc(i, 0) = ColSplit(0)
        RowSrc(i, 1) = ColSplit(1)
        RowSrc(i, 2) = ColSplit(2)
        RowSrc(i, 3) = ColSplit(3)
        RowSrc(i, 4) = ColSplit(4)
        RowSrc(i, 5) = ColSplit(5)
    Next i
    
    '4. Populate the listbox with the RowSrc array
    With Me.ListBox1
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "50;50;50;50;50;50"
        For i = 0 To UBound(RowSrc)
            Me.ListBox1.AddItem (RowSrc(i, 0))
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = RowSrc(i, 1)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = RowSrc(i, 2)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = RowSrc(i, 3)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = RowSrc(i, 4)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = RowSrc(i, 5)
        Next i
    End With
    
End Sub

Chris

 

[johnbird1988]

Hello John,

I haven't tested this thoroughly, but try the following code:

Sub Populate_Listbox()


    Dim RowSplit
    Dim ColSplit
    Dim RowSrc()
    Dim i as Integer
    
    '1. Split the data into rows by the carriage return character (chr(10))
    RowSplit = Split(Worksheets("Sheet1").Range("AR4"), Chr(10))
    
    '2. Prepare the RowSrc array that is used to populate the listbox
    ReDim RowSrc(UBound(RowSplit), 5)
    
    '3. For each set of row data in RowSplit, split this further into the columns (by "|")
    '    and store in the RowSrc array
    For i = 0 To UBound(RowSrc)
        ColSplit = Split(RowSplit(i), "|")
        RowSrc(i, 0) = ColSplit(0)
        RowSrc(i, 1) = ColSplit(1)
        RowSrc(i, 2) = ColSplit(2)
        RowSrc(i, 3) = ColSplit(3)
        RowSrc(i, 4) = ColSplit(4)
        RowSrc(i, 5) = ColSplit(5)
    Next i
    
    '4. Populate the listbox with the RowSrc array
    With Me.ListBox1
        .Clear
        .ColumnCount = 6
        .ColumnWidths = "50;50;50;50;50;50"
        For i = 0 To UBound(RowSrc)
            Me.ListBox1.AddItem (RowSrc(i, 0))
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = RowSrc(i, 1)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = RowSrc(i, 2)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = RowSrc(i, 3)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = RowSrc(i, 4)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = RowSrc(i, 5)
        Next i
    End With
    
End Sub

Chris

Excellent Chris, this worked perfectly. Thank you for your help, John

 

[cdchapman]

Thanks. Glad I could help.

Chris