Wie wird die automatische Vervollständigung beim Eingeben einer Excel-Dropdown-Liste durchgeführt?

Wenn Sie eine Dropdown-Liste zur Datenüberprüfung mit großen Elementen haben, müssen Sie in der Liste nach oben und unten scrollen, um das richtige zu finden, oder das gesamte Wort direkt in das Listenfeld eingeben. Wenn es eine Methode gibt, mit der die automatische Vervollständigung beim Eingeben des ersten Buchstabens in die Dropdown-Liste ermöglicht wird, wird alles einfacher. Dieses Tutorial bietet eine VBA-Methode, mit der Sie dies erreichen können.

Autocomplete beim Eingeben einer Dropdown-Liste mit VBA-Code
Weitere Tutorials für Dropdown-Liste ...

Autocomplete beim Eingeben einer Dropdown-Liste mit VBA-Code

Gehen Sie wie folgt vor, um eine automatische Vervollständigung einer Dropdown-Liste zu erstellen, nachdem Sie die entsprechenden Buchstaben in die Zelle eingegeben haben.

Zunächst müssen Sie ein Kombinationsfeld in das Arbeitsblatt einfügen und dessen Eigenschaften ändern.

1. Öffnen Sie das Arbeitsblatt mit der Dropdown-Listenzelle, die Sie automatisch vervollständigen möchten.

2. Bevor Sie ein Kombinationsfeld einfügen, müssen Sie die Registerkarte Entwickler zum Excel-Menüband hinzufügen. Wenn die Registerkarte Entwickler in Ihrem Menüband angezeigt wird, Fahren Sie mit Schritt 3 fort. Andernfalls gehen Sie wie folgt vor: Klicken Sie auf Reichen Sie das > Options öffnen Options Fenster. In diesem Excel-Optionen Klicken Sie im Fenster Menüband anpassen Überprüfen Sie im linken Bereich die Developer Feld, und klicken Sie dann auf OK Taste. Siehe Screenshot:

3 Klicken Developer > Insert > Kombinationsfeld (ActiveX-Steuerung).

4. Zeichnen Sie ein Kombinationsfeld im aktuellen Arbeitsblatt. Klicken Sie mit der rechten Maustaste darauf und wählen Sie dann Immobilien aus dem Kontextmenü.

5. In dem Immobilien Dialogfeld, bitte ersetzen Sie den Originaltext in der (Name) Feld mit TempCombo.

6. Ausschalten Entwurfsmodus Beim Klicken Developer > Entwurfsmodus.

Wenden Sie dann den folgenden VBA-Code an

7. Klicken Sie mit der rechten Maustaste auf die Registerkarte "Aktuelles Blatt" und klicken Sie auf Code anzeigen aus dem Kontextmenü. Siehe Screenshot:

8. In der Öffnung Microsoft Visual Basic für Applikationen Kopieren Sie den folgenden VBA-Code und fügen Sie ihn in das Codefenster des Arbeitsblatts ein.

VBA-Code: Autocomplete beim Eingeben der Dropdown-Liste

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. Drücken Sie Andere + Q Tasten gleichzeitig zum Schließen der Microsoft Visual Basic-Anwendungen Fenster.

Wenn Sie von nun an auf eine Dropdown-Listenzelle klicken, wird die Dropdown-Liste automatisch angezeigt. Sie können mit der Eingabe des Buchstabens beginnen, um das entsprechende Element in der ausgewählten Zelle automatisch zu vervollständigen. Siehe Screenshot:

Hinweis: Dieser Code funktioniert nicht für zusammengeführte Zellen.

Der Dropdown-Liste mit Kontrollkästchen Nutzen von Kutools for Excel Sie können auf einfache Weise eine Dropdown-Liste mit Kontrollkästchen in einem bestimmten Bereich, einem aktuellen Arbeitsblatt, einer aktuellen Arbeitsmappe oder allen geöffneten Arbeitsmappen erstellen, die Ihren Anforderungen entsprechen.
Laden Sie es herunter und probieren Sie es jetzt aus! (30 Tage kostenloser Trail)

In Verbindung stehende Artikel:

Wie erstelle ich eine Dropdown-Liste mit mehreren Kontrollkästchen in Excel?
Viele Excel-Benutzer erstellen in der Regel eine Dropdown-Liste mit mehreren Kontrollkästchen, um mehrere Elemente pro Zeit aus der Liste auszuwählen. Tatsächlich können Sie mit der Datenüberprüfung keine Liste mit mehreren Kontrollkästchen erstellen. In diesem Tutorial zeigen wir Ihnen zwei Methoden zum Erstellen einer Dropdown-Liste mit mehreren Kontrollkästchen in Excel. Dieses Tutorial bietet die Methode zur Lösung des Problems.

Erstellen Sie eine Dropdown-Liste aus einer anderen Arbeitsmappe in Excel
Es ist recht einfach, eine Dropdown-Liste zur Datenüberprüfung zwischen Arbeitsblättern in einer Arbeitsmappe zu erstellen. Was würden Sie tun, wenn sich die für die Datenüberprüfung benötigten Listendaten in einer anderen Arbeitsmappe befinden? In diesem Tutorial erfahren Sie ausführlich, wie Sie eine Drop-Fown-Liste aus einer anderen Arbeitsmappe in Excel erstellen.

Erstellen Sie eine durchsuchbare Dropdown-Liste in Excel
Für eine Dropdown-Liste mit zahlreichen Werten ist es keine leichte Aufgabe, eine richtige zu finden. Zuvor haben wir eine Methode zum automatischen Ausfüllen der Dropdown-Liste eingeführt, wenn Sie den ersten Buchstaben in das Dropdown-Feld eingeben. Neben der Funktion zur automatischen Vervollständigung können Sie die Dropdown-Liste auch durchsuchbar machen, um die Arbeitseffizienz beim Finden geeigneter Werte in der Dropdown-Liste zu verbessern. Probieren Sie die Methode in diesem Lernprogramm aus, um die Dropdown-Liste durchsuchbar zu machen.

Füllen Sie andere Zellen automatisch aus, wenn Sie Werte in der Excel-Dropdown-Liste auswählen
Angenommen, Sie haben eine Dropdown-Liste basierend auf den Werten im Zellbereich B8: B14 erstellt. Wenn Sie einen Wert in der Dropdown-Liste auswählen, möchten Sie, dass die entsprechenden Werte im Zellbereich C8: C14 automatisch in eine ausgewählte Zelle eingefügt werden. Um das Problem zu lösen, tun Ihnen die Methoden in diesem Tutorial einen Gefallen.

Weitere Tutorials für Dropdown-Liste ...

Die besten Tools für die Office-Produktivität

Kutools für Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
Super Formula Bar (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Vollständige Funktionen 30 Tage kostenlose Testversion. 60 Tage Geld-zurück-Garantie.

Lesen Sie mehr... Kostenfreier Download... Kauf...

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
Steigert Ihre Produktivität um 50% und reduziert täglich Hunderte von Mausklicks für Sie!

Lesen Sie mehr... Kostenfreier Download... Kauf...

290 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
stefany · 10 days ago
hi, thanks for your code. I would like to autocomplete and make multiple selections in a drop down list. Can you help me please?, thank you
- Reply
To post as a guest, your comment is unpublished.
krish · 3 months ago
Hello thanks for the code it works great.
But i want to auto expand drop down list when i move to next cell by pressing enter key without clicking on arrow button (or) alt+down.
Thanks in advance.
- Reply
To post as a guest, your comment is unpublished.
taras.samolyuk · 3 months ago
Hello, is there a chance to add dependency to select date and in the next column to the proposed choices?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 months ago
  
  Good day,
  Would you provide a screenshot of what you are trying to do?
  
  Reply
To post as a guest, your comment is unpublished.
Christiam Alfonzo · 3 months ago
como hago para agregar esta función
=INDIRECTO("tabla1[columna]")
al código
- Reply
To post as a guest, your comment is unpublished.
Danielle · 4 months ago
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?
Kind regards, Danielle
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 4 months ago
  
  Hi,
  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?
  
  Reply
To post as a guest, your comment is unpublished.
coltan123 · 6 months ago
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
- Reply
To post as a guest, your comment is unpublished.
Mhyk · 7 months ago
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.
Thank you and God Bless.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 6 months ago
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Sisi · 7 months ago
Hi Guys,

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?
- Reply
To post as a guest, your comment is unpublished.
Simona Muresan · 7 months ago
Hi Guys,

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?
- Reply
To post as a guest, your comment is unpublished.
Juan Carlos · 8 months ago
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?
- Reply
To post as a guest, your comment is unpublished.
Leonardo · 8 months ago
Hi! Is it possible to disable the combo box scrolling list when I click on the cell? and leave it active only if I click on the arrow, thanks!
- Reply
To post as a guest, your comment is unpublished.
Leonardo · 8 months ago
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!
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 8 months ago
  
  Hi Leonardo,
  The VBA works for all data validation drop down lists in the worksheet.
  
  Reply
To post as a guest, your comment is unpublished.
gurita_22 · 8 months ago
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
- Reply
To post as a guest, your comment is unpublished.
Rohit · 8 months ago
The command works when first applied. But after saving the file, when i reopen it the drop down itself disappears.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 8 months ago
  
  Hi Rohit,
  After applying the code, please save the workbook as an Excel Macro-Enabled Workbook before closing the file.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  wendy · 2 months ago
  
  I have done that - saved as a macro-enabled workbook but it still all disappears when you close the file. Thanks, Wendy
  
  Reply
To post as a guest, your comment is unpublished.
ahmadzaid · 8 months ago
I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’

for info .. I use excel 2010
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 8 months ago
  
  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....
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  ahmad zaid · 8 months ago
  
  It's work in cell with list but when i move or tap or select any cell with no list ..the up message talked you about always show..
  
  Reply
To post as a guest, your comment is unpublished.
Carlos · 9 months ago
Hi! Thanks for this code!

I'm getting a compile error saying "variable not defined", it highlights the "cancel = true" line
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 9 months ago
  
  Hi Carlos,
  Which Excel version are you using?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  ahmadzaid · 8 months ago
  
  I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’
  
  fo info. I use excel 2010
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Carlos · 9 months ago
  
  Hi. Office 365 Pro Plus
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 9 months ago
  
  Hi Carlos,
  Remove the "cancel = true" line from the VBA, then try again.
  
  Reply
To post as a guest, your comment is unpublished.
Mitchell · 9 months ago
Hi Crystal,

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?

MItchell
- Reply
To post as a guest, your comment is unpublished.
Sergio · 10 months ago
Hi,

Is it possible to show the matches with the caracter your typping using this code and format of combox?

Example:

You have a list with Onion, Black Onion, Green Onion, Red Onion...

And in the combox you type: "On", so the drop down list show all the values existing on the list that matchs with "On".

Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 9 months ago
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Reinaldo Yajure · 10 months ago
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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 9 months ago
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Alistair · 7 months ago
  
  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.
  Do you have a solution for this?
  
  Thanks,
  
  Alistair
  
  Reply
To post as a guest, your comment is unpublished.
Sandeep Sharma · 10 months ago
is there any way to increase the list size as it is showing up only 10 line items as i have 12 lines
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 9 months ago
  
  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.
  
  Reply
To post as a guest, your comment is unpublished.
Jonas · 11 months ago
How can I Use Tab/Arrows/Enter to move to another cell once the current cell is filled? Thanks
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 11 months ago
  
  Hi Jonas,
  The Tab key and the Enter key can work normally. But can't help with the Arrows yet. Sorry for the inconvenience.
  
  Reply
To post as a guest, your comment is unpublished.
Ramitha · 11 months ago
Hi . It is a very useful code . However , I cannot use it on horizontal tables which are is a reference for vertical drop downs. Is there a solution ?
- Reply
To post as a guest, your comment is unpublished.
K1000z · 1 years ago
Hi Guys, are you able to share a code which i can use so that the autocomplete is used on the same column i.e H in every sheet within the workbook.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  K1000z · 1 years ago
  
  Looks good.Thanks Crystal.
  
  Reply
To post as a guest, your comment is unpublished.
Billy · 1 years ago
I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Billy,
  Which Excel version are you using?
  
  Reply
To post as a guest, your comment is unpublished.
Angga S.P. · 1 years ago
Woooooooowwwwww very useful, thankyou for sharing, god bless you
- Reply
To post as a guest, your comment is unpublished.
Big.Moe · 1 years ago
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)
- Reply
To post as a guest, your comment is unpublished.
Jerome · 1 years ago
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.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Jerome,
  The Tab key still works well in my case after applying the code. Which Excel version are you using?
  
  Reply
To post as a guest, your comment is unpublished.
Alex C · 1 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Don P. · 1 years ago
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?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Don P.
  Only the 1-MatchEntryComplete option can make it work. Sorry can't help you for that.
  
  Reply
To post as a guest, your comment is unpublished.
Christian · 1 years ago
It works. Thank you and God bless
- Reply
To post as a guest, your comment is unpublished.
Kuku · 1 years ago
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 ?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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.
  
  Reply
To post as a guest, your comment is unpublished.
Charlis · 1 years ago
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 ?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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.
  
  Reply
To post as a guest, your comment is unpublished.
Eze · 1 years ago
Hi! Does Anyone Know, how use this function just on a cell?
- Reply
- To post as a guest, your comment is unpublished.
  Thomas · 1 years ago
  
  Change line 16 to read "If ActiveCell.Address = "{ADDRESS e.g. $C$8}" Then"
  
  Reply
To post as a guest, your comment is unpublished.
ron · 1 years ago
hi! I have tried the code, and it works fine, but it will not allow a macro to copy and paste.
- Reply
To post as a guest, your comment is unpublished.
RyanPlanteNJ · 1 years ago
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?
- Reply
To post as a guest, your comment is unpublished.
MartinK. · 1 years ago
Great tutorial. Thank you.
- Reply
To post as a guest, your comment is unpublished.
me · 1 years ago
Doesn't work. Please update.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  Sorry for the inconvenience. Which Excel version are you using?
  
  Reply
To post as a guest, your comment is unpublished.
Nadine · 1 years ago
hi when i use the VBA code on a column that has dates it changes it to a number. can anyone help
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Nadine,
  The problem didn't cause by the code.
  You need to format the column cells to date format before or after creating the drop-down list.
  
  Reply
To post as a guest, your comment is unpublished.
kennethaugng@gmail.com · 1 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Ben · 1 years ago
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?

Any help is much appreciated
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Donald · 7 months ago
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Jonas · 11 months ago
  
  It doesnt work for me either, I can still enter items that are not on the list. can you please help!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Rodrigo Fernandez La · 1 years ago
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
defrancodominic@gmail.com · 1 years ago
Really great tutorial, thanks. Do you know how I can adapt this code to get it to work for merged cells? Thanks again.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi,
  The code does not work for merged cells yet. Sorry for the inconvenience.
  
  Reply
To post as a guest, your comment is unpublished.
Rob K · 1 years ago
Hi every one i noticed a few questions asking if there is a way to limit this to one column but i didn't notice any answers, was this ever figured out? i have multiple drop downs in my work sheet and they are all being affected by the macro where i only want column H to be affected. Any help with this would be greatly appreciated.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Rob K,
  The below VBA code can help you solve the problem, please have a try and thank you for your comment.
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Dilshan · 16 days ago
  
  Hi,
  I am trying to pull that list data from another sheet but it does not work. below is my code
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'Update by Extendoffice: 20190812
  Dim xCombox As OLEObject
  Dim xStr As String
  Dim xWs As Worksheet
  Dim xArr
  Dim xRgStr As String
  Dim xRg As Range
  xRgStr = "MAINInput!$D:$D"
  'MAINInput = sheet name where the list is available
  
  Set xWs = Application.ActiveSheet
  On Error Resume Next
  Set xCombox = xWs.OLEObjects("cmbEmployee")
  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.cmbEmployee.List = xArr
  End If
  .LinkedCell = Target.Address
  End With
  
  xCombox.Activate
  Me.cmbEmployee.DropDown
  End If
  End Sub
  Private Sub cmbEmployee_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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Lucas Sienk · 1 years ago
  
  Thank you for this fix! One quick question: I running this code across multiple worksheets (31 to be exact) but it is only affecting one. I have the code the same for each worksheet limiting the columns but it doesn't seem to be working on the other sheets. Thanks in advance!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  crystal · 1 years ago
  
  Hi,
  The above code can only work on one sheet at a time. Sorry for the inconvenience.
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Rob K,
  The below VBA code can help you solve the problem. Thank you for your comemnt.
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'Update by Extendoffice: 20190812
  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" 'The range of cells containg drop-down lists you will make them autocomplete
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Denise · 1 years ago
When I paste the VBA code, do I need to change the Worksheet code editor to the Worksheet and Before Double Click? I changed those 2 fields and it populated codes into the code editor screen. So where do I paste the VBA code? after those? Before those? do I delete those 2 codes that autofill and then paste? I tried that but then the Worksheet changed back to General and Declarations and the code doesn't make my cells autofill. What am I doing wrong???
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Denise,
  Just right click the sheet tab (the worksheet contains the drop-down list you will make it autocomplete) and select View Code from the context menu, when the code editor opening, paste the code directly into it.
  
  Reply
To post as a guest, your comment is unpublished.
Chandan Mehta · 1 years ago
How can i use same list for another combo box, do i need to write same code for another combo box, as i require 10 combo box
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Chandan Mehta,
  You just need to create 10 data validation drop-down lists, and then follow the steps in the article to achieve it (only need one combo box in the whole operation).
  
  Reply
To post as a guest, your comment is unpublished.
Ayelet · 1 years ago
Is there a way to make this searchable (so that it returns anything from the list that matches anywhere in text not just the beginning)?
- Reply
To post as a guest, your comment is unpublished.
RK · 1 years ago
How can I set it to accept only the text in the list?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi RK,
  Sorry can't help you with that. Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Anthony · 1 years ago
How can the code be modified such that pressing SHIFT + TAB moves the cursor left in the row?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Anthony,
  Sorry can't help you with that. Thank you for your comment.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Anthony · 1 years ago
  
  No problem. I figured it out. Thanks, Crystal.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  noob · 1 years ago
  
  Yeah want to know too
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  eeframe27 · 1 years ago
  
  Anthony, how did you end up doing this?
  
  Reply
To post as a guest, your comment is unpublished.
Parth · 1 years ago
But what if i have dynamic list ? How can i provide the input to the combobox and refresh everytime the data is been added?
- Reply
- To post as a guest, your comment is unpublished.
  Conor · 1 years ago
  
  You'll have to make a table of the dynamic dataset and assign it to the 'ListFillRange' within the properties of your combobox.
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Hi Parth,
  Sorry can’t help you with this. Welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  
  Reply
To post as a guest, your comment is unpublished.
MR Excel · 1 years ago
Anyone looking for a simple dropdown input form in excell based on a list; i used Data.. "Data Validation" and then choose Allow: "List" and pointed the source at the list.
This does not do the auto complete but does avoid any macros or dev and just uses native simple Excel features.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 1 years ago
  
  Good day,
  The dropdown list mentioned in the post is the Data Validation dropdown list.
  After creating the Data Validation dropdown list in the worksheet, go ahead to apply the steps from 2 to 10 in the post.
  
  Reply
To post as a guest, your comment is unpublished.
Fabian · 2 years ago
Buenas tardes dentro de esa lista predictiva tengo campos de lista con la función indirecto pero me elimina , como puedo conservar las funciones de lista conservando la búsqueda predictiva.
- Reply

Einloggen auf Ihr Konto

Passwort zurücksetzen