Add A List of Unique Values to a Combo Box.

The ComboBox is one of the UserForm’s controls, it is used to store and display a list of items for the user to select from. This control can be configured to accept a pre-defined list entries only, or allow the user to enter his or her own text. It can be very useful to control what the user can enter. To prevent the user from entering unwanted values, in the VB Editor scroll down the ComboBox’s Properties List and set the MatchRequired Property to True. Now the User can only select from the ComboBox’s values. It is simple to populate a ComboBox with the contents of a list using the AddItem method, the Control’s RowSource Property or the List Property of the ComboBox. I find the latter to be the most effective. The purpose of this Tutorial is to show you how to solve the more challenging problem of how to load the unique values from a list to the ComboBox. All the code examples assume that the data for the list is in Column A.

Advanced Filter

The first method uses the AdvancedFilter method of the Range object. AdvancedFilter has a feature that allows you to filter for unique items in a list and copy to another location. This example code copies the unique items from the sample list to the ComboBox by copying them to the extreme right column. There’s little chance this column will have data in it, but the code could easily create a temporary sheet to use instead.

Page Title

Making Excel work for you.

If you find this article useful You can help maintain my site by donating. Simply click the image below

Buy me a coffee

Private Sub UserForm_Initialize() ''/// Variabled for the Excel worksheets that contain the data and the range containing the data Dim oWs As Worksheet Dim rData As Range ''/// the worksheet that contains the data Set oWs = ThisWorkbook.Worksheets("Sheet1") ''//Set the range that contains the items, and then temporarily copy _ the unique values of that data to the last column on the sheet. With oWs Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) rData.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Cells(1, .Columns.CountLarge), _ Unique:=True Me.ComboBox1.Clear ''///load the unique list to the ComboBox Me.ComboBox1.List = .Range(.Cells(2, .Columns.Count), _ .Cells(.Rows.CountLarge, .Columns.CountLarge).End(xlUp)).Value ''///clean up the contents of the temporary stored items .Range(.Cells(1, .Columns.Count), .Cells(.Rows.CountLarge, .Columns.CountLarge).End(xlUp)).ClearContents End With End Sub Populate ComboBox using Advanced Filter

A Collection

Method two uses the Collection object. If you are familiar with VBA, you will probably have used the in-built collections such as the Workbooks, Worksheets, Range and Cells collections. A Collection is an object that holds several similar items. These items can easily be accessed and manipulated, even if there are many items within the collection. The syntax is: collection.Add item, key, before, after A collection cannot have the same key twice so what the code does is create a key using the item that we are adding. This will ensure that we will not get duplicates. The On Error Resume Next is just telling the code to ignore the error we get when we try to add a duplicate and simply move on to the next item to add.

'/// Variabled for the Excel worksheets that contain the data and the range containing the data Dim oWs As Worksheet Dim rData As Range Dim vData As Variant ''///A variant to store the list items Dim cData As New VBA.Collection ''///A collection to store the unique items Dim lCnt As Long ''///The count used in the Loop to populate the collection . Dim vItem As Variant ' '///A variant representing the type of items in cData ' '/// the worksheet that contains the data Set oWs = ThisWorkbook.Worksheets("Sheet1") ''///Get the range of the list in Column A. With oWs Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With ''///Place the list values into an Array. vData = rData.Value ''///Place the list values from the Array into the VBA.Collection. On Error Resume Next For lCnt = 1 To UBound(vData) cData.Add vData(lCnt, 1), CStr(vData(lCnt, 1)) Next lCnt On Error GoTo 0 With Me.ComboBox1 ''///Clear the combo box .Clear ''///Add each unique item from cData to the ComboBox. For Each vItem In cData .AddItem cData(vItem) Next vItem End With End Sub

A VBA Dictionary

A VBA Dictionary is similar to a Collection, but I think more versatile. You can store all kinds of data in it’ numbers, texts, dates, arrays, ranges, variables and objects. Every item in a Dictionary gets its own unique key and with that key you can get direct access to the item. Unlike a Collection you can check if a key exists and so create a list of unique items.

Private Sub UserForm_Initialize() Dim dic As Scripting.Dictionary Dim vData, vItem Dim i As Long Set dic = New Scripting.Dictionary ''/// the worksheet that contains the data Set oWs = ThisWorkbook.Worksheets("Sheet1") ''///Get the range of the list in Column A. With oWs vData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value End With With dic .comparemode = 1 For Each vItem In vData If Not .exists(vItem) And vItem <> "" Then .Add vItem, Nothing Next vItem Me.ComboBox1.Clear If .Count Then Me.ComboBox1.List = .Keys End With End Sub Populate ComboBox using a Dictionary

Please note, to use a Dictionary object you must set a Reference to the Microsoft Scripting Runtime Library

Excel UNIQUE Function

The Excel UNIQUE function can extract a list of distinct values or values that only occur once, i.e. unique values. It can return a unique or distinct list from one column or from multiple columns. Unfortunately, it is currently only available to all Office 365 Subscribers. I found that this function can be used when loading a ComboBox with a list of unique items.

Private Sub UserForm_Initialize() ''/// the worksheet that contains the data Set oWs = ThisWorkbook.Worksheets("Sheet1") ''///Get the range of the list in Column A. With oWs Set rData = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With Me.ComboBox1.Clear Me.ComboBox1.List = Application.WorksheetFunction.Unique(rData) End Sub Populate ComboBox using the UNIQUE Function

Example Workbook

If you want to test the code then you can download the example workbook which contains all the code. I have added a VBA Stopwatch Function which uses Windows API calls to the system’s high–resolution timer. This returns a very accurate time for loading the ComboBox.