Akuini

Macro to create searchable data validation+combobox

Overview Discussion
Akuini

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
5,089
Office Version
  1. 365
Platform
  1. Windows
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...
Click to expand...

Read more about this Excel article...
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
mercer31 said:
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).
Click to expand...
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 www.dropbox.com
 
Akuini said:
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 www.dropbox.com
Click to expand...
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
 
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.
 
Akuini said:
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.
Click to expand...
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
 
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.
 
Alright, I somehow got through the error message, but now I cannot get the Combo box to appear or read the validation list text.

www.dropbox.com

Test (1).xlsm

Shared with Dropbox
www.dropbox.com www.dropbox.com
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

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