Home > Macros > UserForms > Combo Box Excel VBA ComboBox Match Required This Excel VBA example shows how to restrict a combo box in a UserForm, so only items that match a list can be entered. Get the free workbook, to test the macro.

Introduction

This Excel VBA example is based on the instructions for Creating an Excel UserForm With ComboBoxes. On that page, the lists are added to the comboboxes by looping through a named range.

In this example, there are a couple of improvements to the UserForm:

• a simpler technique is used to create the lists.
• the comboboxes are modified so that only items that match a list item can be entered.

Watch the steps in this video, and the written instructions are below.

Create a ComboBox

We will be working with the two comboboxes shown below -- a single column combo box for Location,

and a two-column ComboBox for Parts, showing the Part ID, and the part name..

Single Column ComboBox

The Location combobox is named cboLocation. It should show all the locations from a named range -- LocationList -- on the LookupLists worksheet.

To set the combobox to use LocationList as its source:

1. In the Visual Basic Editor (VBE), select the UserForm, and click on the Location combobox, to select it.
2. In the Properties window, find the RowSource property, and enter LocationList as the source.
   

Multiple Column ComboBox

Next, we'll add items to the Part ComboBox, which using the PartsLookup named range. The Part combobox will have 2 columns -- Part ID and Part description

To set the Part combobox to use PartsLookup as its source:

1. In the Visual Basic Editor (VBE), select the UserForm, and click on the Part combobox, to select it.
2. In the Properties window, find the RowSource property, and enter PartsLookup as the source.
   

   

Prevent Invalid Entries in the ComboBox

To prevent invalid entries in the comboboxes, change the ComboBox properties, so a match is required. This will result in an error message, if an invalid item is entered, and you try to leave the combo box.

NOTE: An alternative is to check for invalid entries, when clicking the Add This Part button -- see the instructions in the next section.

1. Select the Part combobox, and in the Properties window, change the MatchRequired setting to True.
2. Repeat this setting change for the Location combobox.

Check for Invalid Entries in ComboBox

To check for invalid entries in the comboboxes, you can add code in the "Add This Part" button. .

1. Select the Location combobox, and in the Properties window, change the MatchRequired setting to FALSE.
2. Add code to check the ListIndex for the combo box. An example for the Location combo is shown below.

'=====================
Dim lLoc as Long
lLoc = Me.cboLocation.ListIndex

'check for a valid entry
If lLoc = -1 Then
   Me.cboLocation.SetFocus
   MsgBox "Please enter a valid location"
   Exit Sub
End If
'=====================

Add "Select" Item to ComboBox lists

Setting the MatchRequired property to True works well, unless your Excel VBA code tries to clear out the comboboxes, to prepare for a new entry. In this example, when you click the Add This Part button, the code removes the entries from the Part combobox and the Location combobox.

When that happens, an error message appears, warning "Invalid Property Value."

To prevent that error, add a "Select" item at the top of each named range.

Then, in the code, add "Select" to the combobox, after the Add Part button is clicked, instead of leaving the comboboxes blank.

Now, the first item in the combobox drop down list is Select, and only items from the list can be entered.

Get the Sample File

Get the zipped sample Excel UserForm ComboBox Match file to see the completed UserForm and Excel VBA code.

More Links

UserForm with ComboBoxes

UserForm Dependent ComboBoxes

UserForm ComboBox VBA