Macro to create searchable data validation+combobox

mercer31 said:

When you double click one of the other cells in columns “B”, “D”, etc, an error message “This value doesn’t match the data validation restrictions defined for this cell.” will pop up and start creating multiples of what was already in the cell when you cancel the error message. Any way to fix that?

Click to expand...

Akuini said:

When I double-clicked a blank cell in col D and moved the cursor to another cell then the error popped up.
In this case try:
In the other columns (i.e B,D,E), on the data validation Settings tab, check Ignore blank.

In col B, you want to allow multiple entries, so you have to set:
On the Error Alert tab, uncheck mark on "Show error alert after invalid data is entered"

Click to expand...

mercer31 said:

Thanks, I thought I had the error alert already unchecked..

Apologize for all the questions. Just one last one. Right now, the combo box appears to the right of the cell after double clicking. If I want the combo box to appear in the cell that the user selected, what would need to be modified?

Click to expand...

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.Left
            .Visible = True
            .Activate
        End With

End Sub

Akuini said:

Replace Sub toShowCombobox() with this one:

Rich (BB code):

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.Left
            .Visible = True
            .Activate
        End With

End Sub

Click to expand...

mercer31 said:

Thank you!

When it comes to selecting an option in the combo box, for it to show up in the cell, you have to hit enter. Is it possible to change the code so the user can just use the cursor to select the item in the searchable list and have it populate the cell, or is the only way for it to work is have them hit enter?

Click to expand...

entryexitexcel said:

Hi Akuini,
First of all, THANK YOU for coming up with the code that saves many lives
I have a huge list of employee names to choose from and scrolling and searching is not working. Hence, I used your code. I did not make any changes. Like Mercer31, I am encountering the same error "Run-time error '424' Object required in macro Sub get_filterX(). This is when I type something in the combo box.

I am also getting an error "comments may appear after end sub end function or end property". My worksheet already has a VBA code that unhides a bunch of columns based on the number entered in cell A3. For confidentiality purposes, I have removed those columns for now but my original file has them. Underneath this, I pasted your code.

Thanks for looking into this.

Click to expand...

entryexitexcel said:

I have a huge list of employee names to choose from

Click to expand...

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

entryexitexcel said:

I think I just fixed this. I will test and come and then come back to you

Click to expand...

Akuini said:

How many rows is it? more than 100K?
If you're interested you can try the add-in version of this searchable combobox. A member use it for a list with 350K rows, and I amended the code a bit to meet his/her specific requirement.
With this add-in you don't have use vba, you just need to install it.
Here:

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com

Just let me know if you need helps.

Click to expand...

entryexitexcel said:

It's probably around 10K.

Click to expand...

entryexitexcel said:

Can I still try the add-in? and will it work for anyone in my team? or does it have to be me? Thanks!

Click to expand...