Macro to create searchable data validation+combobox

[Akuini]

Akuini submitted a new Excel article:

Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors

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...

Read more about this Excel article...

 

[mercer31]

Akuini submitted a new Excel article:

Macro to create searchable data validation+combobox - Speed up searching in long list data validation & easy to set various specific behaviors



Read more about this Excel article...

Unfortunately, I think you are working on a later version of excel than I have. When I download the excel files, everything is stripped out (i.e. there's no code showing in VBA, no combo box, etc).

 

[Akuini]

Unfortunately, I think you are working on a later version of excel than I have. When I download the excel files, everything is stripped out (i.e. there's no code showing in VBA, no combo box, etc).

I think the downloaded file is corrupted. Sometimes when I download an xlsm file from Mediafire, it somehow change to .xlsx, especially if I download it via Firefox browser.
Try this link from dropbox:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[mercer31]

I think the downloaded file is corrupted. Sometimes when I download an xlsm file from Mediafire, it somehow change to .xlsx, especially if I download it via Firefox browser.
Try this link from dropbox:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I actually just dumped the code you had in the thread and it started to work. Once I added it to my workbook, I got a Object variable or with block variable not set error '91' message in the following location highlighted in green. Any thoughts?

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

 

[Akuini]

Hm, that's strange, unless you have changed anything in the code actually the code will not read Sub get_filterY().
Try removing Sub get_filterY(), see what happens.

 

[mercer31]

Hm, that's strange, unless you have changed anything in the code actually the code will not read Sub get_filterY().
Try removing Sub get_filterY(), see what happens.

I actually highlighted the wrong portion... It was the in the filterX() section.

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

 

[Akuini]

Not sure why that happens.
Could you download the example file from the new link?
There is a description on how to set up this seachable combobx.

 

[mercer31]

Not sure why that happens.
Could you download the example file from the new link?
There is a description on how to set up this seachable combobx

I will try that. Worst case, could I upload a draft version of my file and see if you can spot the discrepancy?

 

[Akuini]

could I upload a draft version of my file and see if you can spot the discrepancy?

Yes.

 

[mercer31]

Alright, I somehow got through the error message, but now I cannot get the Combo box to appear or read the validation list text.

Test (1).xlsm

Shared with Dropbox

www.dropbox.com