Excel VBA - inserting values into multicolumn combobox

Question

I've got two combo boxes on a user form in Excel. When I select the first combo box, it filters the rows on the current worksheet and displays the values in combobox2.

I would like combobox2 to be a multicolumn combobox so I can copy and paste multiple values from one worksheet to another but I can't get multiple values into combobox2. This is the code I've hacked together. I am able to filter the results when I change the value in combobox1 but combobox2 is displaying a single value:

Private Sub ComboBox1_Change()
Me.ComboBox2.Clear

'filter the results in combo box 2 when the user selects the different         options in combo box 1
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim copyFrom As Range, aCell As Range
    Dim lRow As Long
    Dim strSearch As String

Set wb = ThisWorkbook
'~~> Set this to the worksheet where the autofilter is applied
Set ws = wb.Worksheets("RegEvents")

'~~> Filter Column on text in Combo box 1
strSearch = ComboBox1.Value

With ws
    '~~> Remove any filters
    .AutoFilterMode = False

    With .Range("RegEvents_WorksheetData")
        .AutoFilter Field:=Range("RegEvents_Action").Column, Criteria1:="="     & strSearch

        '~~> Identify the filtered range
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible)

        '~~> Add values from filtered search to Combobox2
        For Each aCell In copyFrom

If aCell.Value <> "" And aCell.Column = Range("RegEvents_EventID").Column Then
ComboBox2.AddItem (aCell.Value)
End If

If aCell.Value <> "" And aCell.Column = Range("RegEvents_Event").Column Then
ComboBox2.AddItem (aCell.Value)
 End If

     Next
    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With
End Sub

Private Sub UserForm_Initialize()
'Used to create an array
Dim listItems As Variant
    Me.ComboBox1.ListIndex = -1 ' -1 = no items selected

Label1.Caption = "Action"
Label2.Caption = "Event"

listItems = Range("CatogriesAction")

With ComboBox1
'Loops through the array and only adds non blank values to the combo box
For i = 1 To UBound(listItems, 1)
 If Len(Trim(listItems(i, 1))) > 0 Then
 .AddItem listItems(i, 1) ' populate the listbox
 End If
 Next i

 .ListIndex = 0 ' -1 = no items selected, set to 0 to select the first item
 End With

'Set number of columns for combobox 2
With Me.ComboBox2
 .ColumnCount = 2
 .BoundColumn = 2
 .ColumnWidths = ".5 in; .5 in"
 End With

End Sub

As far as I know a combobox can show multiple columns while it is open but only one column while the combobox is inactive (collapsed). That one column is the "bound" column which can be defined in the properties. Maybe a listbox would be better suited? Like this you can show more columns. — Ralph

Axel Richter Axel Richter · Accepted Answer · 2015-05-26T11:29:53

For multi column ComboBoxes you must use the .List property to fill the columns data. So in your case:

For Each aCell In copyFrom

 If aCell.Value <> "" And aCell.Column = Range("RegEvents_EventID").Column Then
  ComboBox2.AddItem aCell.Value
  lRow = aCell.Row
 End If

 If aCell.Row = lRow And aCell.Column = Range("RegEvents_Event").Column Then
  With ComboBox2
   .List(.ListCount - 1, 1) = aCell.Value
  End With
 End If

Next

A complete Example using .TextColumn displaying both columns in the text field. Requirements: UserForm with ComboBox1

Private Sub UserForm_Initialize()

 With Me.ComboBox1
  .ColumnCount = 3
  .BoundColumn = 2
  .TextColumn = 3
  .ColumnWidths = "1cm;1cm;0"
 End With

 RegEvents_EventID = [{1;2;3;4;5}]
 RegEvents_Event = [{"Event 1";"Event 2";"Event 3";"Event 4";"Event 5"}]

 For i = LBound(RegEvents_EventID) To UBound(RegEvents_EventID)
  With Me.ComboBox1
   .AddItem RegEvents_EventID(i, 1)
   .List(.ListCount - 1, 1) = RegEvents_Event(i, 1)
   .List(.ListCount - 1, 2) = RegEvents_EventID(i, 1) & " " & RegEvents_Event(i, 1)
  End With
 Next

End Sub

Excel VBA - inserting values into multicolumn combobox

1 Answers