ms excel vba populate listbox with varying column requirements

Question

BACKGROUND

I am developing a userform in ms excel to provide a 'dashboard' for data spread over several worksheets. The userform presents a combobox and from that selection, the listbox is populated. The userform also allows the listbox information to be copied by a 'COPY' button. The rowsource for the listbox can be a single column (e.g., Budget!$L$191) or several columns & rows (e.g., JKG.Slave!$I$38:$JM$44).

I have selected the 'MultiSelect' property in the listbox properties.

CHALLENGES

How do I display all rowsource data in a multicolumn (if needed) listbox?
How do I dynamically capture the column count needed to support the multicolumn listbox?
Can I use a variable to capture the column count and have it populate the listbox at runtime?

CODE SAMPLE FROM PROJECT

Public Sub ComboBox1_Change()

Dim cSelect As String
Dim lcount As Integer

cSelect = UserForm2.ComboBox1.Value

UserForm2.ListBox1.RowSource = cSelect

lcount = UserForm2.ComboBox1.ColumnCount

MsgBox lcount

End Sub  

The variable lcount returns one (1) even when the rowsource is the multiple rows & multiple columns selection.

Thank you all for the help.

Answer 1

Here I modified my answer to Excel ComboBox - Autosize Dropdown Only to adjust the Column counts and ListWidths of a ComboBox or ListBox.

Usage

ConfigureComboOrListBox ListBox1

---

Private Sub ConfigureComboOrListBox(LCBox As Object)
    Dim arrData, arrWidths
    Dim x As Long, y As Long, ListWidth As Double
    arrData = LCBox.List
    ReDim arrWidths(UBound(arrData, 2))

    For x = 0 To UBound(arrData, 1)
        For y = 0 To UBound(arrData, 2)

            If Len(arrData(x, y)) > arrWidths(y) Then arrWidths(y) = Len(arrData(x, y))

        Next
    Next

    For y = 0 To UBound(arrWidths)

        arrWidths(y) = arrWidths(y) * LCBox.Font.Size
        ListWidth = ListWidth + arrWidths(y)
    Next

    With LCBox
        .ColumnCount = UBound(arrWidths) + 1
        .ColumnWidths = Join(arrWidths, ";")
        .ListWidth = ListWidth
    End With

End Sub

Answer 2

To add items to the listbox, just define your range and loop through them to add to list.

Now I have just specified an arbitrary range, you can make that range dynamic and to what ever you need and the code will adjust the columns and rows count for you. You don't need to mess with the listbox properties for column count, as it is done programmatically to suit the dynamic nature of your issue.

   Dim x      
   Dim i As Long
   Dim y As Long
   Dim yy As Long

    x = Range("C1:E20") ' change this to suit the range you want       
    y = (UBound(x, 2) - LBound(x, 2))

    ListBox1.ColumnCount = y + 1
    For i = 0 To UBound(x) - 1
      With ListBox1
       .AddItem
        For yy = 0 To y
          .List(i, yy) = x(i + 1, yy + 1)
        Next
      End With
    Next    

Then to get multiple select, change the properties of the list box,