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:
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!
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:
Code:
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!