CODEWITHSUNDEEP
vbaexcellistboxuserformexcel-2016
darkdimension
darkdimension

Reputation: 13

ms excel vba populate listbox with varying column requirements

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.

Upvotes: 0

Views: 2556

Answers (2)

user6432984
user6432984

Reputation:

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

enter image description here

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

Upvotes: 1

KyloRen
KyloRen

Reputation: 2741

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,

enter image description here

Upvotes: 0

Related Questions