Listbox populated based on Combobox

[Tandar]

Hello all,

I'm having lots of trouble with the second step of something I need to do. I have the first part working fine (Populating the combobox with unique values from a range on a sheet). However I believe for the next part I need to create an array (which I am terrible at). Hopefully someone can give me a hand!


Here is what the user will see on a spreadsheet:

Here is what the data looks like (on Sheet2 in same workbook):

Here is the code I have so far when the button is clicked:

Sub PopulateName()
  'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range

    Dim vaData As Variant               'the list, stored in a variant

    Dim ncData As New VBA.Collection    'the list, stored in a collection

    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.

    Dim vaItem As Variant               'a variant representing the type of items in ncData

    'Instantiate the Excel objects.
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Product List")

    'Using Sheet2,retrieve the range of the list in Column A.
    With wsSheet
        Set rnData = .Range("A2:A112")
    End With

    'Place the list values into vaData.
    vaData = rnData.Value

    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
        ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
    Next lnCount
    On Error GoTo 0
    
    'Clear the combo box (in case you ran the macro before),
    'and then add each unique variant item from ncData to the combo box.
    With Worksheets("Instructions").OLEObjects("ComboBox1").Object
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With
End Sub

So once a user selects an item from the Combobox, the Listbox should populate with values from the second column. The first column does contain duplicate values and the second column has different values for some of those duplicates (which need to be present in the listbox).

I'm assuming an array is the easiest option, but if I am wrong then please show me another method! Thank you so much!

 

[Tandar]

Here is a sample file if it helps out and makes it easier to work with the data:

https://www.box.com/s/3616afa26e47145ed416

 

[Tandar]

I got the listbox to update, but only if I press the "Run" command on the debug toolbar. How would I get this to update automatically on Combobox selection??

Option Explicit



Sub Button13_Click()

'clear the contents from the listbox and combobox when pressed
With Worksheets("Instructions").OLEObjects("ComboBox1").Object
        
    .Clear
        
End With

With Worksheets("Instructions").OLEObjects("Listbox1").Object

    .Clear

End With


End Sub


Sub Plants()
  'The Excel workbook and worksheets that contain the data, as well as the range placed on that data
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnData As Range

    Dim vaData As Variant               'the list, stored in a variant

    Dim ncData As New VBA.Collection    'the list, stored in a collection

    Dim lnCount As Long                 'the count used in the On Error Resume Next loop.

    Dim vaItem As Variant               'a variant representing the type of items in ncData

    'Instantiate the Excel objects.
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Product List")

    'Using Sheet2,retrieve the range of the list in Column A.
    With wsSheet
        Set rnData = .Range("A2:A112")
    End With

    'Place the list values into vaData.
    vaData = rnData.Value

    'Place the list values from vaData into the VBA.Collection.
    On Error Resume Next
        For lnCount = 1 To UBound(vaData)
        ncData.Add vaData(lnCount, 1), CStr(vaData(lnCount, 1))
    Next lnCount
    On Error GoTo 0
    
    'Clear the combo box (in case you ran the macro before),
    'and then add each unique variant item from ncData to the combo box.
    With Worksheets("Instructions").OLEObjects("ComboBox1").Object
        .Clear
        For Each vaItem In ncData
            .AddItem ncData(vaItem)
        Next vaItem
    End With
End Sub


Private Sub ComboBox1_Change()
    Dim rCopy As Range
    Dim rList As Range
    Dim rCl As Range
     
    With Worksheets("Product List")
        .AutoFilterMode = False
         'Apply the filter
         'assumes data starts in A2 & is 3 columns
         'filters on Column D
        .Range(.Cells(2, 2), .Cells(.Rows.Count, 3)).End(xlUp).AutoFilter Field:=1, Criteria1:=Worksheets("Instructions").OLEObjects("Combobox1").Object
        With .AutoFilter.Range
            On Error Resume Next
            Set rCopy = .Offset(1, 0).Resize(.Rows.Count - 1, 2) _
            .SpecialCells(xlCellTypeVisible)
            If Not rCopy Is Nothing Then rCopy.Copy .Range("H1")
            .AutoFilter
            Set rList = .Range(.Cells(1, 9), .Cells(1, 9).End(xlDown))
            With Worksheets("Instructions").OLEObjects("Listbox1").Object
                
                For Each rCl In rList
                    .AddItem rCl.Value
                Next rCl
            End With
            .Range("H1").CurrentRegion.Clear
            On Error GoTo 0
        End With
    End With
     
End Sub