Many YouTube visitors wanted to know how to populate a combo-box on an Excel userform with unique items. You see when we enter, for example, items in a worksheet we enter the details like item name, quantity purchased and quantity sold depending on the present situation. So for example when our items reach the re-order value that we have set we purchase or order an item. Similarly if a customer places an order with us we enter the details under the heading quantity sold. At the end of a period (day, month, quarter or year) we would like to know what quantity we purchased, what quantity we ordered and what is the present inventory. Userforms can help to make this visually easier for us. Data can not only be transferred from a userform to a worksheet but also from the worksheet into the userform for great display.
Now in the above situation we may like to display only unique items in our combo-box although we have multiple entries for a single item due to the process of sales and purchase happening on many days. At the same time we may like to display the quantity available quickly and easily.
We can use collections to capture only unique items from our worksheet and place them in our combobox. We can do the same procedure with a listbox. Also using labels we can easily display the quantity available with an appropriate message. The complete VBA code is given below.
Private Sub ComboBox1_Change()
Label1.Caption = “Total” & ComboBox1.Value & ” available”
Label2.Caption = WorksheetFunction.SumIf(Columns(1), ComboBox1.Value, Columns(2)) – WorksheetFunction.SumIf(Columns(1), ComboBox1.Value, Columns(3))
Private Sub CommandButton1_Click()
Private Sub UserForm_Initialize()
Dim myCollection As Collection, cell As Range
On Error Resume Next
Set myCollection = New Collection
For Each cell In Range(“A2:A” & Cells(Rows.Count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
myCollection.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
ComboBox1.ListIndex = 0
Watch the training video also to see how we can populate a combo-box or list-box to select an item and display its quantity:
Creating and Using Collections in VBA