This article brings you relevant knowledge about excel, which mainly introduces related issues about how Excel makes dynamic fuzzy matching drop-down menus. Let’s take a look at it together. I hope it will help Everyone is helpful.
Related learning recommendations: excel tutorial
We know that we can use functions to create fuzzy matching drop-down menus, but the function guy The characteristic is that it is small and smart. If the data volume is slightly larger, the efficiency will be at a critical moment. As we all know, in Excel, to solve complex problems efficiently, you still have to rely on the stupid and stupid VBA. So, today I will share with you how to use VBA to create a more useful dynamic fuzzy matching drop-down menu.
The completed effect demonstration is as follows:
#As shown in the picture above, click on the cell in column A, Excel will automatically pop up a text input box and a list frame. When data is entered in the text box, the data in the list box will be dynamically updated.
Select the target worksheet, go to [Development Tools] → [Insert] → [ActiveX Control], and insert a text box and a list box. The size and storage location of the control are arbitrary. It doesn't matter what you say anyway. The code will make adjustments by itself later.
It should be noted that if you do not know how to adjust the VBA code, then the name of the text box here must be TextBox1, and the name of the list box must be ListBox1 - normal and In other words, these two names are also the system default.
Keep the target worksheet selected, press the shortcut key
See comments for code analysis
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
|
Finally close VBE and save the current workbook as an xlsm file.
……
If you don’t understand VBA, after following the above steps, you need to replace the “example” in the code with the worksheet where the drop-down list data source is located name, replace the "d2:d" & .Cells(Rows.Count, "d") part with the column where the actual data source is located.
1 2 3 |
|
Related learning recommendations: excel tutorial
The above is the detailed content of Teach you step by step how to create a dynamic fuzzy matching drop-down menu in Excel. For more information, please follow other related articles on the PHP Chinese website!