dizzydunham
New Member
- Joined
- Mar 22, 2014
- Messages
- 30
I have managed to populate a combobox on userform from a column in a table with unique items (to remove duplicates.)
I want to do the same with several other comboboxes with items from other columns in the same table.
What do I need to do to the code to make this work. as duplicating some of the below doesn't work!?
Thanks for any help and direction received.
I want to do the same with several other comboboxes with items from other columns in the same table.
What do I need to do to the code to make this work. as duplicating some of the below doesn't work!?
Code:
Sub RAISE_VAF_Click()
'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("VAF Tracker")
'Using Sheet1,retrieve the range of the list in Column 12 company
With wsSheet
Set rnData = .ListObjects("Table1").ListColumns(12).DataBodyRange
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 UserForm2.CBXCompany.Object
.Clear
For Each vaItem In ncData
.AddItem ncData(vaItem)
Next vaItem
End With
Thanks for any help and direction received.