Excel Autocomplete for Large Products List: Filter by First 4 Letters (2000 Items)

Question

Hello, I want to create a list of products in Excel (this is not a problem) but the amount of data on this list is about 2000 items. Is there an option that after entering the beginning of the index of the item (the first 4 letters), it displays to me only those that start with?

adamas_nt · Answer

Depends on the list (data correctness, form, AciveX). Write more precisely or upload a file with an example.

Vencer · Answer

It should look like this:
After pressing the letter "K", I have to display only products beginning with the letter "K" in the list, after entering "ka" only those that start with "ka" etc.

Added after 1 [minutes]:

adamas_nt · Answer

Try with the AciveX combo box. Otherwise, you will have to use an additional cell defining the criteria for the list, an auxiliary column and then downloading the list ...

rychu888 · Answer

Hi,

I dig up the topic due to the fact that I have exactly the same problem now. I would like the drop-down list to expand automatically after entering the cell + narrowed down by entering the next letters. Can someone how to do it?

Regarding the combo function, as I understand it allows you to fill in through fields, and yet for people who can barely catch excel (add, subtract, average, multiply

) may be too laborious; /

Thanks in advance for reply

Maybe this over a year, a solution was found

Regards,
rychu888

adamas_nt · Answer

In this case, the correctness of the data / the list is omitted. Maybe the solution to this topic ?
-sorting the list
-filtration

In the attachment I adapted Sheet2 to the description.
The "active" range is D2

10. The value is saved when the form is closed.
You just need to refine the position of the form: Link In E2007 it is Left + 24, Top + 141 (more or less)
The list is filtered by the first letter entered. Second: the ActiveX combo has built-in autocomplete.

Analyze, adjust to your own needs.

PiterM71 · Answer

Hello,
looking for an auto-complete list, something like this example I found here. I made it to my "own" needs but the "select from list" window does not work as I would like, the list is there, but after entering the first letter the list does not expand, only the first name is. Can you please correct the codes in VBA, because I'm not good at it :wink:

Thank you. Regards
Peter

Maciej Gonet · Answer

I don't understand exactly what the problem is, because in my opinion the list itself works fine. When the text field is activated and you enter the first letter, the list is set to the first password that matches this letter, and typing the next letters moves the list according to these letters. You can also opt out of entering letters at any time and select an item from the list with the mouse. I have Excel 2010.

PiterM71 · Answer

... maybe I didn't specify it, it's about sheet 2, in sheet 1 it is OK, but the useform is fixed and I would like it to be assigned to each cell as in sheet 2, Thank you

clubs · Answer

@ PiterM71 Buddy, see to userforma2 that you have not completely rewritten / copied the code from userforma1 you have an error in Private Sub ComboBox1_Change compare it you will find

PiterM71 · Answer

Thanks for the tip! but I have another problem or even two:
1) I increased the data list (1000 items) and I can see that UserForm appears with a small delay, with about 3000 it is very slow, but there is no help for that?
2) after entering the data in 30 lines, I move the view of the sheet down to some positions and when I want to enter the next item, the Userform appears somewhere at the bottom of the sheet or outside its view and then it is only possible to close it with the combination alt + ctrl + del :-o

, maybe you can set it up somehow, but for now the VBA language is black despair for me. Many thanks in advance for your help. Example in appendix
Peter

adamas_nt · Answer

Ad.1 Take a look at the last line of UserForm_Initialize. There is an assignment of a ComboBox list to an array. I would reverse it. I would use this table globally, populating it when I open the file. In UserForm2, instead of adding a value to the ComboBox's list in a loop, I would assign an array. The file will open a bit longer, but the initiation of the form will be "lightning".

Ad.2 Remove / modify (or condition) the line 'Me.Top ...' from 'UserForm_Activate' (UserForm2).

PiterM71 · Answer

THANKS again! From point 2 it's ok, I deleted but 1 , I tried but nothing works, can you ask for a ready-made command? Thank you. Regards

adamas_nt · Answer

Easiest ... load 'UserForm1' on opening and business done ;) Have a look at 'ThisWorkbook', 'Module1' and in the code 'UserForm2' of course.

PiterM71 · Answer

Thank you very much ! Regards

AnnaMind · Answer

Hi!
My experience with VBA is small, so I am asking you for a hint.

I wanted to use this code, I'm interested in the second sheet version. This is to be used to complete individual cells in the "TEST1" column.
Should the code UserForm2 + assigning UserForm to selected cells be enough?

How can I define "tbl" in UserForm2 code to make it work properly?

adamas_nt · Answer

1. Copy the code from Initialize uForm1 to Initialize uForm2
2. With Open, load uForm2
3. when closing, hide the form (do not close) and clear the ComboBox

As a result, something like the attachment will come out. UserForm1 is redundant in this case.

AnnaMind · Answer

Thank you very much! When I went to my file, I did a good job, but I forgot about Module1, it's always better to ask. Now the problem is that when I want to select something again, the list is empty, which code fragment is responsible for clearing this list?

adamas_nt · Answer

When hiding, the content of the ComboBox is cleared. At this point, tblTmp is empty (i = 0 in Change). Of course, if you start typing (Change), the list will fill up.

To complete this, a list must be assigned in the case of i = 0 with Change. Add a line after Combo.Clear

Log in, to see the code

ppwilk · Answer

Colleagues, I am a complete layman in the matter of Excel (I only know the basic functions), I am trying to create an invoice template for my business, I only miss the list of items that I have sold so far, just saved in the auto-complete list.

The file you created is really sufficient, but you'd be kind enough to explain to me how to copy these formulas to my template.

Thank you in advance !

Excel Autocomplete for Large Products List: Filter by First 4 Letters (2000 Items)

Post #1

Post #2

Post #3

Post #4

Post #5

Post #6

Post #7

Post #8

Post #9

Post #10

Post #11

Post #12

Post #13

Post #14

Post #15

Post #16

Post #17

Post #18

Post #19

Post #20

Topic summary