Prevent Invalid Entries in Excel UserForm

In an Excel UserForm, you can use combo boxes, to show users a drop down list of items. If all goes well, the users will select an item from the list, and life will be good.

However, the occasional user likes to get creative, and types an entry that isn’t in the drop down list. And that can cause problems!

To restrict those creative users, you can set the MatchRequired property for the combo box to True.

userformcombomatch02

Add a Select Item

Changing that MatchRequired property can cause problems though. In this example, the Excel VBA code also tries to clear out the combo boxes, after adding an order to the parts database sheet.

Because an empty string isn’t a valid entry, an error message appears — “Invalid Property Value”.

userformcombomatch03

To prevent that error from appearing, you can add “Select” as the first item in the drop down list, and reset the combo boxes to that item, instead of clearing them out.

userformcombomatch01

Download the Sample Combo Box Workbook

To download the sample file, go to the Excel Templates page on my Contextures site. In the UserForms section, look for UF0026 – Prevent Invalid Entries in UserForm.

The file is in Excel 2003 format, and zipped. Enable macros after opening the workbook.

Watch the Excel UserForm Combo Box Match Video

To see the steps for changing the MatchRequired setting, and editing the code, you can watch this short Excel video tutorial.

_________________

0 thoughts on “Prevent Invalid Entries in Excel UserForm”

  1. Debra,
    Setting the “Style” property of a combobox to 2 (fmStyleDropDownList) is another way.
    The user then cannot alter the list.

    On the other hand, if there is an item in the list that you don’t want the user to select, how does one handle that?
    I set the listindex to an alternate if the item is selected…

    Select Case
    Case 8
    ‘”—–“
    .ListIndex = 7
    End Select

    Seems like there should be a built-in method to do that?

  2. @Jim,

    Why would you include an item in the ComboBox that should not be selected… isn’t that kind of against the reason ComboBoxes exist? Why not simply set up logic to clear the ComboBox and then re-populate it with properly selectable values in response to whatever your trigger conditions are?

  3. @Jim, thanks for the Style property suggestion, I’ll try that in one of my forms.

    In this example, the code checks the selected item, before adding it to the database.

  4. @Jim,

    Ah, okay, now I see what you want. Give this a try. Assuming your ComboBox is named ComboBox1, copy/paste the following code into the UserForm module…

    ‘****************************START OF CODE****************************
    Dim SKIP As Boolean, KEYSTROKE As Boolean

    Private Sub ComboBox1_Click()
    SKIP = Not ComboBox1.Text Like “*[!-]*”
    If SKIP And Not KEYSTROKE Then
    ComboBox1.ListIndex = ComboBox1.ListIndex + 1
    End If
    End Sub

    Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    KEYSTROKE = True
    End Sub

    Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    KEYSTROKE = False
    If KeyCode = 38 And SKIP Then
    ComboBox1.ListIndex = ComboBox1.ListIndex – 1
    ElseIf KeyCode = 40 And SKIP Then
    ComboBox1.ListIndex = ComboBox1.ListIndex + 1
    End If
    End Sub
    ‘****************************END OF CODE****************************

    This code will allow you to have as many separators as you want so long as they are never the first or last item in the list and so long as two separators are never next to each other in the list. One thing though… when keying the up or down arrow, the user will see the selection stop on the separator briefly before moving on. If they click the separator with the mouse, the visual effect is much cleaner. Also, when the click it with the mouse, I chose to move the selection down to the item below the separator (seemed logical to me as clicking with the mouse does not have an indicated direction of motion like using the arrow keys do). Oh, by the way, as written, a separator is considered any item composed of all dashes, no matter how many dashes that is.

  5. Hi, is it possible to store the date in different cells? For example: I have 3 categories, Hard, Liquid and Gas. If my first option is Hard then data will enter from A5, if it’s Liquid then from A105, and if it’s Gas then the data will store from A205. Is it possible by VBA?
    It’s almost same as the tutorial above. I’ll enter 4 data in the User Form, but the data will be stored in different cells, according to their category (which should be my first option on the user form)

  6. How do you prevent Select from being a valid entry that populates to the worksheet. I need to require the user to choose from the drop down, but not allow select as an acceptable choice.

Leave a Reply to Rifat Ahmed Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.