This Excel VBA example shows how to restrict a combo box in a UserForm, so only items that match a list can be entered. Download the free workbook, to test the macro.
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:
Watch the steps in this video, and the written instructions are below.
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..
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:
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:
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.
To check for invalid entries in the comboboxes, you can add code in the "Add This Part" button. .
'===================== 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 '=====================
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.
Download the zipped sample Excel UserForm ComboBox Match file to see the completed UserForm and Excel VBA code.
Last updated: December 5, 2016 12:22 PM
Contextures RSS Feed