VBA Possibly: Searchable list while using data validation through a named range in a table

Hello,

So I have a question about how to make a searchable list that shows the remaining options while typing. The dropdown list is great but with over 100 options it could be easier just searching/typing.

I have looked online and tried to use some Combo Box with vba code (and i have tried 3 different ones) but I always seem to run into a problem.

For instance:

1) Using named range in a table, the options with the vba code and the combo box would not bring over the list in the box rather it was just blank.
2) While using just the range as in like "A1:A50", this would work when I would use the combo box, but it would list the whole range including total cell (Yeah I know I could just not include that row but...). However, month to month another name could be added to the list and with out resetting the data validation 3 different times I am trying to keep it dynamic which is why I originally used the named range in the table.
3) Also tried creating a new named range using formulas such as offset and counta to keep it dynamic, but with the vba code, it then would only bring over the actual formula as options. So it would only show the offset and the other parameters as options and not the actual list.

If the other vba code is needed, let me know and I can put it up, but thinking maybe someone has attempted this while using a table and/or named range with some vba and maybe I can just adjust it to my workbook.

The workbook overall is done for the year, so for this next fiscal year I am updating it to add a few more things to help us out, so I am willing to adjust the file as well.

Please let me know if I should provide the file.

Thanks for any help,
Ant

How about a searchable combobox like this:

Thanks for the quick feedback. I think this might work. I am reviewing what all I would need to change.

I think this may be a problem that I did not mention. The whole file uses tables.

Please Login or Register  to view this content.

In this instance, I would grab the data for that month and paste it in its required table, so meaning the table would expand with more rows. The data validation is an added column on the very end of the table and based on the detail of the data, I select which category of the list it belongs in.

So the data (selections once put into table) is growing month to month as well with the list. @Akuini Is there a way to change that "B2:B20" range to a reference of a column from a table?

Thanks (This macro will be helpful in some of my other work though),
Ant

Maybe something like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Can you attach a sample workbook?

Ah, sorry, I don't think what I suggested above will work.
But if below the table you don't have any data then you can specify the range longer than your table's row.
For example: if the last row of table is 1000 then you can specify xCell to be' say "B2:B20000", so when the table is expanding then you don't need to adjust it.

@Akuini Okay, yeah I will attach it. I cleaned up some of the file, and left only the Q1 data so you can see what it is doing.

Tabs 4300,4310,4810 are where selections are done once data is entered into the table. Balance tab is where my list is from, and from there I have a named range called Restriction_Items that I use for data validation in the three other tabs in their respective tables in the restriction column.

The list is manually adjusted for now so I have to add a row to it if I am adding a new restriction to the list.

Please let me know if you need more detail or have any other thoughts!!

Thanks,
Ant

If the above approach doesn't work then maybe something like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note: I haven't read your sample workbook yet, but I will.

Oh my... working like a charm with the code above.

This was a must to be defined:

Please Login or Register  to view this content.

This piece of the code was a little different: It actually worked in both sections (Combobox1_GotFocus & Worksheet_SelectionsChange) you mentioned independently. So I guess both options were okay.

Please Login or Register  to view this content.

Once again, thanks for all the help!! I really appreciate it @Akuini

Thanks,
Ant

You're welcome, glad to help, & thanks for the feedback.

If you want another way (I think it's more efficient):

I think the better approach is using Private Sub Worksheet_Activate() to specify the xCell.
The thing is if you open the book and it goes directly to say sheet 4300-PRF, then it won't trigger the Sub Worksheet_Activate() for this sheet, so we need to force another sheet to open first (e.g "Directions"), we can do this using Private Sub Workbook_Open() in ThisWorkbook code module.

And since you already have a named range for the list (i.e 'Restricted_Items') then I made some changes to use the named range.

Note: since you have a sorted list then I commented this line: dar.Sort
so it won't be redundant.

in the sheet module:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

in ThisWorkbook code module:

Please Login or Register  to view this content.

Try the combobox in sheet 4300-PRF:

Hmm okay that makes sense. Thanks for looking more into it. That file seems to work fine, I will have to compare both to see if I notice a difference.

I could see leaving out the sorting stuff but why would it be more efficient to define it this way? Not trying to be difficult just curious of why?

Also what do you mean by we have to force the directions page to open to have it work. Meaning with this new way?

I was able to add the previous code that worked to each of the sheets 4300,4310,4810 and it seemed to work even when I saved and closed the file and reopened. But I will definitely try it out.

Thanks for all the options, I know there are usually several ways to do things so it is really beneficial for my knowledge that you are doing this!!!

Thanks,
Ant

Actually, after trying the last approach I think the Private Sub Worksheet_Activate() won't work as intended.
Let me see what I can do tomorrow, I think using a named range for col Restriction would be better.
Well, it's past midnight in my part of the world, time to sleep.

Sounds good! Thanks for taking the time out of your sleep for this, I am looking forward to see what you might have planned! Haha sleep easy

Well, I think ‘Private Sub Worksheet_SelectionChange’ is the best way to determine the range of the combobox location. So the method in post #8 will be suitable for you.
But just for learning purpose I tried a bit different method, still using ‘Private Sub Worksheet_SelectionChange’ but also adding a named range.
So I create a named range "Restriction_4300", the source is column Restriction in Table4300, and use it here:

Please Login or Register  to view this content.

This piece of the code was a little different: It actually worked in both sections (Combobox1_GotFocus & Worksheet_SelectionsChange) you mentioned independently. So I guess both options were okay.

You just need to use it in Worksheet_SelectionsChange, if you also use it in Combobox1_GotFocus, it still works but it would be redundant.
If you use it only in Combobox1_GotFocus then it won’t work.

Note:
If you want to change some of the combobox property, you can modify this part:

Please Login or Register  to view this content.

Okay yeah that is where I left it at but wow, thanks. I always have trouble with named ranges while using vba so this a big help for some other files.

I imagine it is just preference on the user working with the file and looks cleaner, but is it better to making a named range for a table column rather than just calling the table and its column like you previously had?

Thanks for the detail I usually just use the properties, but I like how this can be adjusted and how you have it pop up to the side is neat!

I imagine it is just preference on the user working with the file and looks cleaner, but is it better to making a named range for a table column rather than just calling the table and its column like you previously had?

Well, having a named range means the defined range is already there so we can use it right away, while without it we need to define the range first before we use it.
Good luck with your project & thanks for the feedback.

@Akuini,

Wondering if you could help me out with this. The input selected is only used when the "Enter" key is pushed or in the code by Case 13. Now it has been very helpful in my work and I appreciate it. But I was wondering if you were able to get it to work on a "double click" rather than "Enter" key, so you could type out what you need and select it from there? Or both would be best? Just a thought. Please let me know if this is doable! If not I greatly appreciate your help in the past as I will continue to use this macro!

I believe this where it would be changed/adapted.

Please Login or Register  to view this content.

Thanks,
Ant

Originally Posted by AMoreno

@Akuini,
But I was wondering if you were able to get it to work on a "double click" rather than "Enter" key, so you could type out what you need and select it from there? Or both would be best?

Actually you can type out what you need and select it from there by using Enter Key (provided the value exists in the list). But when I try your sample workbook there's a trailing space in one entry i.e "OH ", so when I type "OH" then hit enter then it raised an error.
So you need to trim all the extra spaces in your data, and you can use this macro to do that (put it in a code module, and the sheet Balances must be the activesheet:

Please Login or Register  to view this content.

Anyway, if you still want to also use double-click then just add this sub:

Please Login or Register  to view this content.

And there's another thing, you can choose how the list is shown in the combobox, i.e:
1. You can have the list sorted.
In Sub ComboBox1_DropButtonClick() and Sub ComboBox1_Change(), uncomment this line:

Please Login or Register  to view this content.

2. You can use the search pattern as "word*word" (not "*word*word"), it means that if you type e.g "A" as the first letter then it only shows entries with the first letter "A".
In Sub ComboBox1_Change() you can choose it by uncommenting one line and commenting the other line.

Please Login or Register  to view this content.

Here's an example (sorted list & use word*word*):

Okay got it. Thanks but I am not for sure if it is working properly. When trying to select the option in the combo box, it still only enters onto the sheet with the press of the "Enter" key.

Now that I try to mess with it, as soon as you click the option in the combo box, it brings it up to the only option, could that be why the double does not work and have to use enter? (Attached pic below once I select option with mouse click)

Attachment 643813

But thanks for the tips, I definitely like the full search pattern. It's neat and has been helpful in my other work.

Thanks,
Ant

Your attachment doesn't work, use Manage Attachments to open the upload window.
Don’t use the paper clip icon to attach your file, it doesn’t work.

Okay thanks, here it is:

You mean after you pick a item then double clicking the combobox doesn't do anything?
I tried it again and it works for me.
I don't know why it doesn't work for you.

Well I surprise myself everyday, I was trying to double click the selection and not the actual box once one is selected. So it does work, apparently I just needed the extra explanation.

Thanks so much Akuini!

You're welcome, glad it sorted out.