Search code examples
excelvbacomboboxlistboxuserform

VBA Display in a listbox based on two dynamic comboboxes

I would like to display the result of 2 comboboxes in a listbox but I don't know how to do it...

Here's what I've got so far :

UserForm with comboboxes Depending on the left-hand combobox, it implies the results of the right-hand combobox.

1/ I would like to display the package version ($E) and the package release ($F) of the package name selected on the right-hand combobox for the project selected on the left combobox, ("monitoring" in this example).

2/ If I select "ALL" package name, I would like to display $E and $F.

Here is my current code :

Private Sub CommanButton1_Click()
    Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet, rCell As Range, Key
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Set ws = Worksheets("system-packages-installed")
    UserForm1.ComboBox1.Clear
    For Each rCell In ws.Range("B2", ws.Cells(Rows.Count, "B").End(xlUp))
        If Not Dic.exists(LCase(rCell.Value)) Then
            Dic.Add LCase(rCell.Value), Nothing
        End If
    Next rCell
    UserForm1.ComboBox1.AddItem "ALL"
    For Each Key In Dic
        UserForm1.ComboBox1.AddItem Key
    Next
End Sub

Private Sub ComboBox1_Click()
    Dim rCell As Range, Key
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Set ws = Worksheets("system-packages-installed")
    UserForm1.ComboBox2.Clear
    For Each rCell In ws.Range("B2", ws.Cells(Rows.Count, "B").End(xlUp))
        If rCell.Value = ComboBox1.Value Then
            If Not Dic.exists(LCase(rCell.Offset(, 1).Value)) Then
                Dic.Add LCase(rCell.Offset(, 1).Value), Nothing
            End If
        End If
    Next rCell
    UserForm1.ComboBox2.AddItem "ALL"
    For Each Key In Dic
        UserForm1.ComboBox2.AddItem Key
    Next

End Sub

Private Sub ComboBox2_Click()
    'This is a test'
    With UserForm1.ListBox1
        .ColumnCount = 27
        .ColumnWidths = "50"
        .RowSource = "'system-packages-installed'!E:E"
    End With
End Sub

If you have any question, do not hesitate to ask me.

Thanks in advance.

Edit :

Here is the table :

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<table class="tg">
  <tr>
    <th class="tg-0pky">System_id<br></th>
    <th class="tg-0pky">Machine</th>
    <th class="tg-0pky">package_name</th>
    <th class="tg-0pky">package_epoch</th>
    <th class="tg-0pky">package_version</th>
    <th class="tg-0pky">package_release</th>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">acl</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">2.2.51</td>
    <td class="tg-0pky">14.el7</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">alsa-lib</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">1.1.6</td>
    <td class="tg-0pky">2.el7</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">apg</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">2.3.0b</td>
    <td class="tg-0pky">24.el7</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">apr</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">1.4.8</td>
    <td class="tg-0pky">3.el7_4.1</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">apr-util</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">1.5.2</td>
    <td class="tg-0pky">6.el7</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">at</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">3.1.13</td>
    <td class="tg-0pky">24.el7</td>
  </tr>
  <tr>
    <td class="tg-0pky">1000010000</td>
    <td class="tg-0pky">monitoring</td>
    <td class="tg-0pky">audit</td>
    <td class="tg-0pky"></td>
    <td class="tg-0pky">2.8.4</td>
    <td class="tg-0pky">4.el7</td>
  </tr>
</table>

Solution

  • My rule is never use RowSource. Generally, if you can put the information in an array, then you can assign that array to the Lisbtobx.List property. The other method is to add a new item with AddItem and then fill out the rest of the columns with the List() property. Below is an example. I made some minor changes to your existing code and the ComboBox2_Change code I think is what you're interested in.

    Private Sub ComboBox1_Change()

    Dim rCell As Range, ws As Worksheet
    Dim dc As Scripting.Dictionary

    Set dc = New Scripting.Dictionary
    dc.Add "ALL", "ALL"

    Set ws = Worksheets("system-packages-installed")
    Me.ComboBox2.Clear
    For Each rCell In ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)).Cells
        If rCell.Value = Me.ComboBox1.Value Then
            If Not dc.exists(LCase(rCell.Offset(0, 1).Value)) Then
                dc.Add LCase(rCell.Offset(0, 1).Value), LCase(rCell.Offset(0, 1).Value)
            End If
        End If
    Next rCell

    Me.ComboBox2.List = dc.Items

End Sub

Private Sub ComboBox2_Change()

    Dim rCell As Range
    Dim ws As Worksheet

    Set ws = Worksheets("system-packages-installed")
    Me.ListBox1.Clear
    For Each rCell In ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)).Cells
        If LCase(rCell.Value) = LCase(Me.ComboBox1.Value) Or Me.ComboBox1.Value = "ALL" Then
            If LCase(rCell.Offset(0, 1).Value) = LCase(Me.ComboBox2.Value) Or Me.ComboBox2.Value = "ALL" Then
                With Me.ListBox1
                    .AddItem rCell.Value 'column 1
                    .List(.ListCount - 1, 1) = rCell.Offset(0, 1).Value 'column 2
                    .List(.ListCount - 1, 2) = rCell.Offset(0, 3).Value 'column 3
                    .List(.ListCount - 1, 3) = rCell.Offset(0, 4).Value 'column 4
                End With
            End If
        End If
    Next rCell
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet, rCell As Range

    Dim dc As Scripting.Dictionary

    Set dc = New Scripting.Dictionary
    dc.Add "ALL", "ALL"

    Set ws = Worksheets("system-packages-installed")
    Me.ComboBox1.Clear
    For Each rCell In ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)).Cells
        If Not dc.exists(LCase(rCell.Value)) Then
            dc.Add LCase(rCell.Value), LCase(rCell.Value)
        End If
    Next rCell

    Me.ComboBox1.List = dc.Items

End Sub