This guide will explain how you can populate a combo box with unique values only in Excel.
Adding a combo box in your spreadsheet allows you to give the user a dropdown list of items for them to choose from.
The Excel combo box we’ll be using is an example of an ActiveX control. Compared to UserForm controls, these controls have properties and can be used as objects in VBA code.
Let’s look at a quick example where we will need to get unique values in an ActiveX Control combo box.
Suppose you have a list of values that you want to use to populate a combo box. Since the list of values comes from user input, the list may contain duplicates. How can we set up a combo box so the choices only contain unique values?
We can use a custom VBA script that can go through each value in a range to check if the value has already been added to the combo box.
Now that we know what to do to populate a combo box with unique values, let’s look at a sample spreadsheet with a working script.
A Real Example of Using a Combo Box with Unique Values in Excel
Let’s take a look at a real example of a combo box being used in an Excel spreadsheet.
In the example below, we have a list of email addresses in column A. To the right of the list is a combo box. A script was used to populate it with all the unique email addresses mentioned in column A.
Since other scripts can reference ActiveX controls, a user’s input in a combo box can be used in other VBA scripts.
You can make your own copy of the spreadsheet above using the link attached below.
Since combo boxes require the Visual Basic Editor, you must use the Excel Desktop App to work with them in your spreadsheets. Users cannot work with UserForms in the Microsoft Excel Online application.
If you’re ready to create your own UserForms dialog box in Excel, let’s begin writing it ourselves!
How to Populate Combo Box with Unique Values only in Excel
This section will guide you through each step needed to begin populating a combo box with unique values in Excel. You’ll learn how we can add custom VBA code to the combo box element to filter out duplicate values from a specified source.
Follow these steps to start adding the combo box to your sheet:
- First, we must insert the combo box into our spreadsheet. Click on Developer > Insert > Combo Box (ActiveX Control). Afterward, drag your mouse to the area you want to place your combo box. The user can draw the element to any size they prefer.
- Next, right-click on the combo box and select the View Code option.
- The Microsoft Visual Basic for Applications window will now pop up onscreen. Paste the custom VBA code into the code editor. After that, press the F5 key to run the code. A prompt will appear asking for a source range.
- Select the range you want to use to populate your combo box. In this example, we will populate the combo box with a list of email addresses found in the range A2:A23. Click on the OK button to finalize the source range.
- Users can now use the combo box to search for a particular email address. Our custom code automatically removes duplicate values from the list.
Frequently Asked Questions (FAQ)
- Why should I use an ActiveX combo box instead of a UserForm combo box?
ActiveX controls show up as objects that can be used in VBA code. If the user interacts with an ActiveX combo box, a script can programmatically select the combo box and retrieve the necessary properties. ActiveX controls provide more flexibility and customization if your use case requires advanced scripts.
- What is the difference between a list box and a combo box?
The difference between the two is mostly visual. A list box displays values or choices. The combo box works similarly but hides the list from the user. The user must click on a drop-down arrow to see the full list of choices. A combo box also allows the user to search values through a text field.
That’s all you need to remember to start populating a combo box with unique values in Excel. This step-by-step guide shows how easy it is to add custom code to an ActiveX combo box to remove duplicates from a source range.
ActiveX controls function is just one example of advanced controls you can place in a macro-enabled Excel spreadsheet. Other ActiveX controls you can use in your sheets include list boxes, checkboxes, and text boxes. With so many other Excel functions out there, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.