- Excel Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 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:
The code:
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
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:
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 SubThe 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
