Contextures

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

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,

Excel ComboBox One Column

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

Excel ComboBox Multiple Column

Single Column ComboBox

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

Location List

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.

RowSource property

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

PartsLookup range

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.

    RowSource property

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.

MatchRequired property

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.

MatchRequired property

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

Invalid Property Value

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

Select item in list

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

Select item in list

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

Select item in list

Download the Sample File

Download 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

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 7, 2016 7:46 PM
Contextures RSS Feed