• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Akuini

Macro to create searchable data validation+combobox

Excel Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the list when the list is long. This searchable data validation+combobox is a way to deal with that problem. It has these behavior:
1. The combobox can appear and hide by certain action e.g double-clicking a cell.
2. You can type some keywords in the combobox and the list will be narrowed down as you type.
3. You only need 1 combobox for all cells with data validation.


I've answered a few questions about this topic in several threads (here's one of them Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?). There's been a lot of feedback indicating the need to change various behaviors applied to it. So I've made some adjustment to hopefully make the code more reliable, faster, and flexible (easy to set various specific behaviors). And now I want to share 3 version of them.

1. VERSION A - ACTIVATED BY DOUBLE-CLICK

How it works:
- The cells in the blue area (col B,D,E) has data validation (with List type)
- Double-clicking a cell in the blue area will activate the combobox
- Type some keywords to search, separated by a space, e.g "f ca"
- The result will be narrowed down as you type.
- The search ignores the keywords order, so the keyword "ma la" would match "Maryland" and "Alabama".
- Use up-down arrow to select an item, hit ENTER, the selected item will be inserted into the cell, and the combobox will be hidden.
- To leave the combobox: hit TAB or ESC
- Numeric values in the list will be treated as text

Image:
COMBOBOX+DAVAL-searchable,dictionary,ON-OFF- formula-1.jpg


The code:
VBA Code:
Option Explicit
'=================================================================================================
'=============== ADJUST THE CODE IN THIS PART: ===================================

'where the cursor go after leaving the combobox
' ofs1 As Long = 1 means 1 row below
' ofs2 As Long = 1 means 1 column to the right
Private Const ofs1 As Long = 0
Private Const ofs2 As Long = 1

' NOTE: you might adjust combobox property in Sub toShowCombobox()

'-------- Do not change this part --------------
Private vList
Private nFlag As Boolean
Private xFlag As Boolean
Private d As Object
Private oldVal As String

Private Sub CommandButton1_Click()
xFlag = Not xFlag
    If xFlag = False Then
        If ComboBox1.Visible = True Then ComboBox1.Visible = False
    End If
        ActiveCell.Offset(ofs1, ofs2).Activate
        Application.EnableEvents = True
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ComboBox1.Visible = True Then ComboBox1.Visible = False

If Target.Cells.CountLarge = 1 And xFlag = False Then
    'if activecell has data validation type 3
    If isValid(Target) Then Call toShowCombobox: Cancel = True
End If

End Sub

'=================================================================================================

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ComboBox1.Visible = True Then ComboBox1.Visible = False:  vList = Empty

End Sub

Function isValid(f As Range) As Boolean
    Dim v
    On Error Resume Next
        v = f.Validation.Type
    On Error GoTo 0
    isValid = v = 3
End Function

Private Sub ComboBox1_GotFocus()
Dim dar As Object, x
    
    With ComboBox1
        .MatchEntry = fmMatchEntryNone
        .Value = ""
            
            Set dar = CreateObject("System.Collections.ArrayList")
            Set d = CreateObject("scripting.dictionary"): d.CompareMode = vbTextCompare
           
            vList = Evaluate(ActiveCell.Validation.Formula1)
            If IsError(vList) Then GoTo skip
            For Each x In vList
                d(CStr(x)) = Empty
            Next
            If d.Exists("") Then d.Remove ""
            
            For Each x In d.keys
                dar.Add x
            Next
               dar.Sort
               'vList becomes unique, sorted & has no blank
               vList = dar.Toarray()
               .List = vList
               .DropDown
               dar.Clear: d.RemoveAll

    End With

Exit Sub
skip:
MsgBox "Incorrect data validation formula", vbCritical
ActiveCell.Offset(, 1).Activate
End Sub


Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
nFlag = False
With ComboBox1
    Select Case KeyCode
        
        Case 13 'Enter
                If IsNumeric(Application.Match(.Value, vList, 0)) Or .Value = "" Then
                        Application.EnableEvents = False
                        ActiveCell = .Value
                        Application.EnableEvents = True
                        ActiveCell.Offset(ofs1, ofs2).Activate
                Else
                        MsgBox "Wrong input", vbCritical
                End If

        Case 27, 9 'esc 'tab
                ActiveCell.Offset(ofs1, ofs2).Activate
        
        Case vbKeyDown, vbKeyUp
             nFlag = True 'don't change the list when combobox1 value is changed by DOWN ARROW or UP ARROW key
        
    End Select
End With
End Sub

Sub toShowCombobox()

Dim Target As Range

Set Target = ActiveCell

        With ComboBox1
            .Height = Target.Height + 5
            .Width = Target.Width + 10
            .Top = Target.Top - 2
            .Left = Target.Offset(0, 1).Left
            .Visible = True
            .Activate
        End With

End Sub


Private Sub ComboBox1_Change()

With ComboBox1
    
    If nFlag = True Then Exit Sub
    If Trim(.Value) = oldVal Then Exit Sub
            
            If .Value <> "" Then
                    
                    Call get_filterX
                    .List = d.keys
                    d.RemoveAll
                    .DropDown
    
            Else 'if combobox1 is empty then get the whole list
                    
                    If Not IsEmpty(vList) Then .List = vList
                    
            End If
    
    oldVal = Trim(.Value)
End With

End Sub

Sub get_filterX()
'search without keyword order
Dim i As Long, x, z, q
Dim v As String
Dim flag As Boolean
    
    d.RemoveAll
    z = Split(UCase(ComboBox1.Value), " ")

    For Each x In vList
        flag = True: v = UCase(x)
            For Each q In z
                If InStr(1, v, q, vbBinaryCompare) = 0 Then flag = False: Exit For
            Next
        If flag = True Then d(x) = Empty
    Next

End Sub

Sub get_filterY()
'search with keyword order
Dim x
Dim tx As String
    
    d.RemoveAll
    tx = UCase("*" & Replace((ComboBox1.Value), " ", "*") & "*")
    For Each x In vList
        If UCase(x) Like tx Then d(x) = Empty
    Next

End Sub


Sub toEnable()
Application.EnableEvents = True
End Sub


The workbook:
xCOMBOBOX+DAVAL-dict+arraylist,ON-OFF,double click 1.xlsm

2. VERSION B - ACTIVATED AUTOMATICALLY
The combobox will pop up automatically whenever you select a cell with data validation (with List type).
The workbook:
xCOMBOBOX+DAVAL-dict+arraylist,ON-OFF,automatic 1.xlsm

3. VERSION C - ACTIVATED BY PRESSING ALT+RIGHT
In this version, in the range with data validation (with List type) you can activate the combobox by pressing a shortcut i.e ALT+RIGHT. Outside the range the shortcut will return to its original function (if it has any).
You can change the shortcut in this part in "module1":
Public Const xdvKey As String = "%{RIGHT}"
for example you can change it to F1 like this: "{F1}"
The workbook:
xCOMBOBOX+DAVAL-dict+arraylist,ON-KEY 1.xlsm

NOTES:
If you need some modification to apply it to your workbook, just let me know. For example:
- if your list is big, say more than 10K rows, then you probably experience a lag. I can modify the code to deal with it, e.g by limiting number of items loaded into the combobox.
- the code uses ArrayList object, so you need Net Framework 3.5 version installed on your Windows (even if you have a later version). If you can't use Arraylist then I can modify the code not to use it, but you'll need a helper column.
- if you're using Excel 365 or later then I can modify the code to utilize SORT & UNIQUE function so we don't need to use Arraylist.

NOTES:
To make the code more reliable & faster applied to your workbook I would need some info:
1. Which version of the searchable combobox are you using?
2. How many rows does the list have?
3. Is the list dynamic?
4. Is the list already sorted?
On the 3 versions above I assume the list isn't sorted, that's why I use Arraylist, it's to sort the list. If the list is already sorted then we don't need the Arraylist, so the code would run faster.
5. What formula are you using on the data validation?

NOTES:
1. One caveat of using macro is when macro changes/writes something on sheet it will delete Undo Stack, so at that time you can't use UNDO. In this case it happens every time the combobox is activated.
2. In the example workbook, please read sheet INFO on HOW-TO implement the searchable combobox on your workbook.
3. The code is a bit complicated because it involves several event-triggered procedure, so there might be a bug that I'm not aware of. So any feedback to make the code more reliable & faster is very welcome.

Notes:
When you first time open the sample workbook then click "Enable Editing", you probably run into en error i.e "Run time error 1004". If it happens then just hit "End" & then continue using the workbook.

Regards,
Akuini
Author
Akuini
Views
6,409
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from Akuini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top