Як здійснити автозаповнення під час набору тексту у випадаючому списку Excel?
Якщо у вас є розкривний список перевірки даних із великими елементами, вам потрібно прокручувати вгору та вниз у списку, лише щоб знайти правильний, або ввести ціле слово безпосередньо у поле списку. Якщо є спосіб дозволити автозаповнення під час введення першої літери у випадаючому списку, все стане простіше. Цей підручник надасть метод VBA, який допоможе вам його досягти.
Автозаповнення під час введення в розкривному списку коду VBA
Будь ласка, виконайте наступні дії, щоб зробити розкривний список автозаповненням після введення відповідних літер у клітинку.
По-перше, вам потрібно вставити комбіноване поле в аркуш і змінити його властивості.
1. Відкрийте аркуш, що містить клітинку випадаючого списку, яку потрібно зробити автозаповненням.
2. Перш ніж вставляти поле зі списком, потрібно додати вкладку Розробник на стрічку Excel. Якщо на вашій стрічці відображається вкладка Розробник, перейдіть до кроку 3. В іншому випадку зробіть наступне: Клацніть філе > опції відкрити опції вікно. У цьому Параметри Excel вікна, натисніть Налаштувати стрічку на лівій панелі перевірте Розробник , а потім клацніть на OK кнопку. Дивіться знімок екрана:
4. Намалюйте комбіноване поле на поточному аркуші. Клацніть правою кнопкою миші, а потім виберіть властивості з меню, що клацне правою кнопкою миші.
5 В властивості діалоговому вікні, будь ласка, замініть оригінальний текст у (Ім'я) поле с TempCombo.
6 Вимкніть Режим дизайну натиснувши Розробник > Режим дизайну.
Потім застосуйте наведений нижче код VBA
7. Клацніть правою кнопкою миші на поточній вкладці аркуша та натисніть Переглянути код з контекстного меню. Дивіться знімок екрана:
8. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наведений нижче код VBA у вікно коду робочого аркуша.
Код VBA: автозаповнення під час введення в розкривному списку
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
9. прес інший + Q клавіші одночасно, щоб закрити Програми Microsoft Visual Basic вікна.
Відтепер, при натисканні на клітинку випадаючого списку, випадаючий список автоматично запитує. Ви можете почати вводити лист, щоб відповідний елемент автоматично заповнився у вибраній комірці. Дивіться знімок екрана:
Примітка: Цей код не працює для об’єднаних комірок.
Легко створюйте розкривний список із прапорцями в Excel:
Як створити випадаючий список із декількома прапорцями в Excel? Багато користувачів Excel, як правило, створюють випадаючий список із декількома прапорцями, щоб вибирати кілька елементів зі списку за раз. Насправді ви не можете створити список із кількома прапорцями за допомогою перевірки даних. У цьому підручнику ми покажемо вам два способи створення випадаючого списку з декількома прапорцями в Excel. Цей посібник пропонує метод вирішення проблеми.
Створіть випадаючий список з іншої книги в Excel Створити випадаючий список перевірки даних серед робочих аркушів у книзі досить просто. Але якщо дані списку, необхідні для перевірки даних, знаходяться в іншій книзі, що б ви зробили? У цьому посібнику ви дізнаєтесь, як детально створити випадаючий список з іншої книги в Excel.
Створіть розкривний список для пошуку в Excel Для випадаючого списку з численними значеннями пошук правильного - непроста робота. Раніше ми запровадили метод автоматичного заповнення випадаючого списку при введенні першої літери у випадаючому вікні. Окрім функції автозавершення, ви також можете зробити пошук у розкривному списку для підвищення ефективності роботи при пошуку належних значень у розкривному списку. Щоб зробити пошук у розкривному списку спробним, скористайтеся методом у цьому посібнику.
Автоматично заповнювати інші комірки під час вибору значень у спадному списку Excel Скажімо, ви створили випадаючий список на основі значень у діапазоні комірок B8: B14. Вибираючи будь-яке значення зі спадного списку, ви хочете, щоб відповідні значення в діапазоні комірок C8: C14 автоматично заповнювались у вибраній комірці. Для вирішення проблеми методи з цього посібника допоможуть вам.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
It works as it should be except for two things, first, there is no validation after insertion. i.e. if I typed anything at the combo then clicked enter it will accept the typed value while it should not do this since the data validation is being used to prevent such behaviors and make sure that the entered data is from selected range. Second, at the data validation list, sometimes I use big range with empty cells and select ignore empty at validation list but the combo takes all the range and shows it, will be nice to remove the empty cells from the combo range.
Thanks & hope you can implement these things to make it perfect.
Buenas! Me ha funcionado de maravilla, pero digamos que solo quiero que aplique a una cierta cantidad de listas validadas, no a todas las que se encuentren o agregue a la hoja...como detengo esta función para que afecta a otras lista validadas en la hoja?
Hi, thanks for your code. I have a problem, It's not working. My data for the validation list is in other sheet (Also is a excel table Ctrl + T because I need enter new data every day), I dont know if any of these are the problem. Can you help me with this?
@Crystal I'm having the same problem. One of my lists uses an INDIRECT formula (based on what they choose in the first column) to generate the list. With this code, now the formula appears instead of the list.
I tried to use this method. I added the code. After that I add the range needed. Looks all fine. Then I test and the combobox dissapears directly after filling it. So the code works, but its not usable. What do I do wrong?
The code works well in my case. It is not easy to figure out the problem as you described above. Would you mind sending your worksheet to my email address?
Code is working , but data validation is not working. For example, when i set the data validation to be red/bue/yellow, it does not show error message when i type green
Hi I have some difficulties with my excel work. I would like to know if there is a method to autofill the cell I desire?
Example
1 = san pedro
2 = santa rosa
3 = calamba
4 = san pablo
and so on.
I have a list with correspoding numbers. If I input number 1 in Cell A5 I would like it to automatically fill the Cell B5 with its corresponding list name (san pedro) and if I input 3 in A5, B5 will be = calamba.
thank you. for the help, Im having trouble to find a solution to this hope to find answers.
Hi, Methods in this tutorial can do you a favor: How To Auto Populate Other Cells When Selecting Values In Excel Drop Down List? https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html
I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?
I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?
Hello, I have a problem with this method and I already use a list validated with an indirect function to refer to the range of a table and when applying this macro it shows me only the indirect formula in the list! How could I solve this problem?
Hello, is it possible to replicate this in a complete column? I mean that each individual row of a column has this possibility or at least 1000 rows in a simple way. Thank you!
If you use this method with one combobox/sheet i guess it's flawless.
I used this method with three comboboxes on one sheet with 3 different ListFillRange. Dropdown keep showing up on different combobox than what i'm typing in. 1. I type and select correctly from the first. 2. When i go to second combo, the third dropdown pops up.(I have to select the"", as it is still empty) then i can search my second combobox. 3. When i go to the third, the first dropdown pops up, I reselect the only item(as there is only one that was already selected in the first step) After this i can add the content in the third dropdown.
All subs are on sheet1's vba window. Now i'm trying to spit code in different modules, but can't really make it work. Search works, but i always have to push the dropdown arrow to see available results to select. Dropdown doesn't pop up at all.
using 365, but i have to make "sheet" work on excel 2010
Hi, I tried the VBA in Excel 2010 and it works well. The drop down list should be data validation drop-down list which is created by clicking Data > Data Validation....
Absolutely perfect and thank you for the code. However when i press enter the cell automatically goes down. How can I make the cell go to the right when I press enter?
Hi Sergio, The method in this article can help you: How To Create A Searchable Drop Down List In Excel? https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html
When I applied this code to my worksheet, the Undo function is disabled (but only for this sheet in the workbook) -- is there a way to fix this so that undo can still be used?
Hi Reinaldo Yajure, Please apply the below VBA code. The problem is fixed. Sorry for the inconvenience.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/05/19 Dim xCombox As OLEObject
Dim xStr As String Dim xWs As Worksheet
Dim xArr
Set xWs = Application.ActiveSheet
On Error Resume Next If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False Cancel = True
xStr = Target.Validation.Formula1 xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox .ListFillRange = ""
.LinkedCell = "" .Visible = False
End With With xCombox
.Visible = True .Left = Target.Left
.Top = Target.Top .Width = Target.Width + 5
.Height = Target.Height + 5 .ListFillRange = xStr
If .ListFillRange = "" Then xArr = Split(xStr, ",")
Me.TempCombo.List = xArr End If
.LinkedCell = Target.Address End With
xCombox.Activate Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode
Case 9 Application.ActiveCell.Offset(0, 1).Activate
Case 13 Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Hi there, this is a great solution, but for some reason, in the original code, the combox was hidden when the cell with the data validation was not active, and only visible when the cell was selected.
With this code, we get undo back, which is perfect, but the combox does not auto-hide again when you click off.
Hi Sandeep Sharma, In the Properties dialog box of the Combo box (step 5 of this tutorial), please find the ListRows field and then change the number 8 to 12 under the Alphabetic tab.
Hi crystal this works great, but I cannot tab or enter to move to the next cell. I have found no command that lets me move to another cell. I have to click the next cell to categorize the column. I have used the code updated 2020/05/19 above in the comments. Please help :)
Hi,
If you only want the column H to be affected, please try the below VBA code. But it is only available in one worksheet. For multiple sheets, you need to add the code to each sheet code window. Hope I can help.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2019/8/14
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr
Dim xRgStr As String
Dim xRg As Range
xRgStr = "H:H"
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
Set xRg = Intersect(Range(xRgStr), Target)
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub
Love this! Thanks so much. Got a small issue though. I like to tab from one cell to the next, but when I get to the cell with the drop down list, I can tab into the cell with no problem, but once I'm in, it won't let me tab back out. I have to physically move the mouse over and click into the next cell. Any fix for this? Kind of disrupts my rhythm, which is why I loved this autocomplete function in the first place. ;o)
This is a great find, however, I have one little issue: I like to tab through the cells as I am entering data. I can tab into the cell with the drop down menu just fine, but once I'm in that cell, it stops me from exiting the cell with a tab. I have to physically move the mouse to the next cell and click on it. Is there a fix to be able to simply tab in and tab out of the autocomplete cell? Thanks.
I am using this code and a data validation with drop down lists for easy acces to what i need because i need the data to remain in excel boxes for other formulas to use it. However one of the combo boxes has almost 1500 items in the drop down and I'd like the user to be able to start typing a word in the combo box and get a list of suggestions to choose from based on the word entered for example if they type 'joh' in the combo box I want to show them a list of all the choices in the drop down that contains 'joh' so they can select the one they want. Now i can find the first 'joh' but it shows me like a list with what is near this 'joh', not specific all that contain 'joh'. Can i modify this code so can be used for what i want? Thank you and please help
The Combobox works fine when 1-MatchEntryComplete is selected ,when I changed the selection to 2-fmMatchEntryNone in the Combobox properties it did not work ,i.e It does not auto select as I start typing. Is there a fix for this?
The code working but whenever I click on cell there the drop list auto prompt ,this make me unable to copy the contents and paste somewhere , how can I enable the copy and paste ?
Hi Kuku,
When the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
I try copy the whole cell of droplisting after all selection , but whenever I click on cell it auto show the listing , this has cost me unable to copy the cells and paste the contents somewhere , any solutions , pls help ?
Hi Charlis,
Actually when the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
Hey guys, I'm having an issue where it doesn't let me type what I want to search for letter by letter. It just shows the whole word automatically and highlighted, and I start to type it moves it to the next closest word. is there anyway to not have it highlight the whole thing?
Hi, Thaks for the code. Could I check if this formula can include an ignore blank function? This code took away my ignore blank function for my data validation. Please advise thanks
Hey, thanks for this! I have managed to get it to work, but like in Cartson's comment: I need to keep selections to the list. It populates ok, but if I enter a word not in the list, it still accepts it. The VBA code you replied with doesn't work unfortunately
Like in Data Validation/locked cell, can it create some error or not allow the typed entry not in the list?
Hi Ben,
Sorry there are mistakes in the previous reply.
After inserting the combo box, open its Properties window, change the Name to TempCombo, and then select 2 - fmStyleDropDownList from the Style field, and finally apply the below code. From now on, it won't allow entering word not in the list.
Dim xRg As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim I As Long
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("TempCombo")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
Set xRg = Target
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.TempCombo.DropDown
End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim xSel As Range
On Error Resume Next
Select Case KeyCode
Case 13
xRg.Offset(0, 1).Select
End Select
End Sub
Hi Cristal, great answer. Now if on the same worksheet I want to add another combo box that calls data from a third sheet, how would the code look like as I tried and got no result. I will appreciate your support. Donald
Hello Crystal,
I have tried to do this... but it doesn't work... I have 4 lists 2 independent and the other two depend on others... It only works with the first list
