Macro to create searchable data validation+combobox

[Akuini]

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?

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"

 

[mercer31]

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"

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?

 

[Akuini]

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?

Replace Sub toShowCombobox() with this one:

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

 

[mercer31]

Replace Sub toShowCombobox() with this one:

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

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?

 

[Akuini]

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?

Sorry, I don't know how to amend the code to do that.

 

[entryexitexcel]

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.

 

[entryexitexcel]

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.

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

 

[Akuini]

I have a huge list of employee names to choose from

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

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

Just let me know if you need helps.

 

[entryexitexcel]

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.

It's probably around 10K. Can I still try the add-in? and will it work for anyone in my team? or does it have to be me? Thanks!

 

[Akuini]

It's probably around 10K.

That's fine, you won't experience any lag.

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

I share it as freeware, so anybody can use it. I'm happy if you & your team like using it. Hope it is useful.
I would also like if you can provide feedback to improve its functionality.