Prompt to Add New Items to ComboBox or Data Validation

Prompt to Add New Items to ComboBox or Data Validation

Microsoft Access ComboBoxes have a handy NotinList event which allow you to check whether a value entered in a combobox is already in its list. If it’s not you can ask the user whether to add it. This post shows how to mimic that functionality in a combobox on a VBA userform. I also show how to do the same thing with a data validation list.

hat ComboBox

Creating a ComboBox NotInList Event

The key to doing this is checking the value of the ComboBox’s “MatchFound” property in its Exit event. If no match is found, we ask the user whether to add the item to the list of valid items (hats in this case). If the answer is “Yes” then a row with the hat is added to the table. If not, we clear the combobox and keep the focus on it. You can see this in action in the video above.

Here’s the code for the combobox’s Exit event:

Private Sub cboHats_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim loValidationSource As Excel.ListObject
Dim loRow As Excel.ListRow

'the Table with the list of valid hats
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
With Me.cboHats
    'We're only interested if these aren't true
    If .MatchFound Or .Value = "" Or .Value = STARTING_VALUE Then
        Exit Sub
    End If
    'If the hat entered isn't in list, prompt to add it
    If MsgBox(.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
        Set loRow = loValidationSource.ListRows.Add
        loRow.Range.Cells(1).Value = .Value
        SortSourceTable
        RefreshComboList
    Else
        'if "no", keep focus on the ComboBox and set it's value to "Choose a hat"
        Cancel = True
        Me.cboHats.Value = STARTING_VALUE
    End If
End With
End Sub

One important thing is that the combobox’s “MatchRequired” property must be set to False (which is the default). Otherwise the Exit will be preempted by an “Invalid Property Value” message from Excel.

Creating a Data Validation NotInList Event

As with the combobox version, we use an event to prompt the user whether to add an item that’s not in the list. This time we use our own “MatchFound” function to check against the data validation’s source list. Similar to setting the “Match Required” combobox property to False, the data validation version requires that the “Show error alert after invalid data is entered” is unchecked in the data validation setup dialog. This is obviously not the default:

data validation setup

Since I’m working in Excel 2010, I’ve created a single-column table (listobject) to hold the valid items. I then simply pointed the data validation’s Source property at the column, excluding the header. Because the source is in a table, it’s dynamic – it adjusts when you add or remove items from the column. No dynamic ranges are required, just select the cells:

data validation source list

Here’s the code from the ThisWorkbook module, which contains the Workbook_SheetChange event and the MatchFound function:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Excel.Range
Dim loValidationSource As Excel.ListObject
Dim loHatCollection As Excel.ListObject
Dim loRow As Excel.ListRow

'wsTables is the sheet's CodeName
If Not Sh Is wsTables Then
    Exit Sub
End If
Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set loHatCollection = wsTables.ListObjects("tblHatCollection")
'only continue if change is in column with data validation
If Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange) Is Nothing Then
    Exit Sub
End If
With Intersect(Target, loHatCollection.ListColumns("Hat Type").DataBodyRange)
    For Each cell In .Cells
        If MatchFound(cell.Value) = False And cell.Value <> "" Then
            If MsgBox(cell.Value & " is not in the list. Add it?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
                Set loRow = loValidationSource.ListRows.Add
                loRow.Range.Cells(1).Value = cell.Value2
            Else
                cell.ClearContents
            End If
        End If
    Next cell
End With
SortSourceTable
End Sub

Function MatchFound(ValueToCheck As Variant) As Boolean
Dim loValidationSource As Excel.ListObject
Dim ValidationList As Excel.Range

Set loValidationSource = wsTables.ListObjects("tblValidationSource")
Set ValidationList = loValidationSource.ListColumns("Hats Validation List").DataBodyRange
MatchFound = Application.WorksheetFunction.CountIf(ValidationList, ValueToCheck) > 0
End Function

And here’s what it looks like in action:

data validation prompt

The Sort object – Excel 2007 Onwards

My code uses VBA’s Sort object, which appeared in Excel 2007. I like the way it works. You add Sort Fields, just as you do in the user interface, and then apply the sort when needed. If you are using Excel 2003 or earlier you’d need to re-write the two sorting procedures to work with your version.

Also, if you are using Excel 2003 or earlier, see this Contextures post for a non-table way of automatically adding items to a data validation list. You could easily add the code to prompt the user whether to do so.

Download

Here’s a workbook with all the code for both versions.

3 thoughts on “Prompt to Add New Items to ComboBox or Data Validation

  1. Interesting – the downside with the validation dropbox, if my memory serves me, is that you lose the undo capabilities of Excel on that sheet when you use the sheet_change event. I always struggle with whether it is worth giving that up.

  2. Can you now take that further and produce another combo box (or drop down list) which is dependent on the items of the first combo box? It too should have the facility to add new items that are not on the list as per the first combo box.

    eg: 1st combobox list Asia, Oceania, Europe, N.America, S. America, Africa

    2nd combobox Under Asia-China, India, Malaysia Japan. Under Europe – Sweden, Germany, France, Italy, etc
    3rd Combobox Under India – New Dehli, Kolkatta, Agra, etc

    So if we look up Asia, and find Korea is not listed so we add it, then we add Seoul, Pusan on the 3rd combobox.

    Can this be done? Best regards

Leave a Reply to Charlie Cancel reply

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>