Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe automatisch aanvullen tijdens het typen in de vervolgkeuzelijst Excel?

Als u een vervolgkeuzelijst voor gegevensvalidatie heeft met grote items, moet u omhoog en omlaag scrollen in de lijst om de juiste te vinden, of u kunt het hele woord rechtstreeks in de keuzelijst typen. Als er een methode is om automatisch aanvullen toe te staan ​​bij het typen van de eerste letter in de vervolgkeuzelijst, wordt alles gemakkelijker. Deze tutorial biedt een VBA-methode om u te helpen dit te bereiken.

Automatisch aanvullen bij het typen in vervolgkeuzelijst met VBA-code
Meer tutorials voor vervolgkeuzelijst ...

Automatisch aanvullen bij het typen in vervolgkeuzelijst met VBA-code

Ga als volgt te werk om een ​​vervolgkeuzelijst automatisch aan te vullen nadat u de overeenkomstige letters in de cel hebt getypt.

Ten eerste moet u een keuzelijst met invoervak ​​in het werkblad invoegen en de eigenschappen ervan wijzigen.

1. Open het werkblad met de cel van de vervolgkeuzelijst die u automatisch wilt laten aanvullen.

2. Voordat u een keuzelijst met invoervak ​​invoegt, moet u het tabblad Ontwikkelaar aan het Excel-lint toevoegen. Als het tabblad Ontwikkelaar op uw lint wordt weergegeven, ga naar stap 3. Ga anders als volgt te werk: Klik op Dien in > Opties om de te openen Opties venster. In deze Excel-opties venster klikt Lint aanpassen Controleer in het linkerdeelvenster het Ontwikkelaar vak en klik vervolgens op het OK knop. Zie screenshot:

3. klikken Ontwikkelaar > Invoegen > Combo Box (ActiveX-besturingselement).

4. Teken een keuzelijst met invoervak ​​in het huidige werkblad. Klik er met de rechtermuisknop op en selecteer Woningaanbod vanuit het rechtsklikmenu.

5. In de Woningaanbod dialoogvenster, vervangt u de originele tekst in het (Naam) veld met TempCombo.

6. Zet de ... uit Ontwerpmodus door te klikken Ontwikkelaar > Ontwerpmodus.

Pas vervolgens de onderstaande VBA-code toe

7. Klik met de rechtermuisknop op de huidige bladtab en klik Bekijk code vanuit het contextmenu. Zie screenshot:

8. In de opening Microsoft Visual Basic voor toepassingen venster, kopieer en plak de onderstaande VBA-code in het codevenster van het werkblad.

VBA-code: automatisch aanvullen tijdens het typen in de vervolgkeuzelijst

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. druk op anders + Q toetsen tegelijk om het Microsoft Visual Basic-toepassingen venster.

Vanaf nu, wanneer u op een vervolgkeuzelijstcel klikt, wordt de vervolgkeuzelijst automatisch gevraagd. U kunt beginnen met het typen van de letter om het corresponderende item automatisch in de geselecteerde cel te laten voltooien. Zie screenshot:

Opmerking: Deze code werkt niet voor samengevoegde cellen.

Maak eenvoudig een vervolgkeuzelijst met selectievakjes in Excel:

Het Vervolgkeuzelijst met selectievakjes nut van Kutools for Excel kan u helpen eenvoudig een vervolgkeuzelijst te maken met selectievakjes in een opgegeven bereik, huidig ​​werkblad, huidige werkmap of alle geopende werkmappen op basis van uw behoeften.
Download en probeer het nu! (30 dagen gratis parcours)

Gerelateerde artikelen:

Hoe maak ik een vervolgkeuzelijst met meerdere selectievakjes in Excel?
Veel Excel-gebruikers hebben de neiging om een ​​vervolgkeuzelijst te maken met meerdere selectievakjes om meerdere items uit de lijst per keer te selecteren. Eigenlijk kunt u met Gegevensvalidatie geen lijst met meerdere selectievakjes maken. In deze zelfstudie laten we u twee methoden zien om een ​​vervolgkeuzelijst met meerdere selectievakjes in Excel te maken. Deze tutorial biedt de methode om het probleem op te lossen.

Maak een vervolgkeuzelijst vanuit een andere werkmap in Excel
Het is vrij eenvoudig om een ​​vervolgkeuzelijst voor gegevensvalidatie te maken tussen werkbladen in een werkmap. Maar wat zou u doen als de lijstgegevens die u nodig hebt voor de gegevensvalidatie in een andere werkmap terechtkomen? In deze zelfstudie leert u hoe u in detail een vervolgkeuzelijst kunt maken vanuit een andere werkmap in Excel.

Maak een doorzoekbare vervolgkeuzelijst in Excel
Voor een vervolgkeuzelijst met talloze waarden is het vinden van een goede geen gemakkelijke taak. Eerder hebben we een methode geïntroduceerd voor het automatisch aanvullen van de vervolgkeuzelijst wanneer u de eerste letter in de vervolgkeuzelijst invoert. Naast de functie voor automatisch aanvullen, kunt u de vervolgkeuzelijst ook doorzoekbaar maken om de werkefficiëntie te verbeteren door de juiste waarden in de vervolgkeuzelijst te vinden. Probeer de methode in deze tutorial om de vervolgkeuzelijst doorzoekbaar te maken.

Vul automatisch andere cellen in bij het selecteren van waarden in de vervolgkeuzelijst van Excel
Stel dat u een vervolgkeuzelijst heeft gemaakt op basis van de waarden in celbereik B8: B14. Wanneer u een waarde in de vervolgkeuzelijst selecteert, wilt u dat de overeenkomstige waarden in celbereik C8: C14 automatisch in een geselecteerde cel worden ingevuld. Om het probleem op te lossen, zullen de methoden in deze tutorial je een plezier doen.

Meer tutorials voor vervolgkeuzelijst ...

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
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.
    defrancodominic@gmail.com · 2 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.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      The code does not work for merged cells yet. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Rob K · 2 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.
    • To post as a guest, your comment is unpublished.
      crystal · 2 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
      • To post as a guest, your comment is unpublished.
        Dilshan · 8 months 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
      • To post as a guest, your comment is unpublished.
        Lucas Sienk · 2 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!
        • To post as a guest, your comment is unpublished.
          crystal · 2 years ago
          Hi,
          The above code can only work on one sheet at a time. Sorry for the inconvenience.
    • To post as a guest, your comment is unpublished.
      crystal · 2 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
  • To post as a guest, your comment is unpublished.
    Denise · 2 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???
    • To post as a guest, your comment is unpublished.
      crystal · 2 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.
  • To post as a guest, your comment is unpublished.
    Chandan Mehta · 2 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
    • To post as a guest, your comment is unpublished.
      crystal · 2 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).
  • To post as a guest, your comment is unpublished.
    Ayelet · 2 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)?
  • To post as a guest, your comment is unpublished.
    RK · 2 years ago
    How can I set it to accept only the text in the list?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi RK,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Anthony · 2 years ago
    How can the code be modified such that pressing SHIFT + TAB moves the cursor left in the row?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Anthony,
      Sorry can't help you with that. Thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Anthony · 2 years ago
        No problem. I figured it out. Thanks, Crystal.
        • To post as a guest, your comment is unpublished.
          noob · 2 years ago
          Yeah want to know too
        • To post as a guest, your comment is unpublished.
          eeframe27 · 2 years ago
          Anthony, how did you end up doing this?
  • To post as a guest, your comment is unpublished.
    Parth · 2 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?
    • To post as a guest, your comment is unpublished.
      Conor · 2 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.
    • To post as a guest, your comment is unpublished.
      crystal · 2 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.
  • To post as a guest, your comment is unpublished.
    MR Excel · 2 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.
    • To post as a guest, your comment is unpublished.
      crystal · 2 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.
  • 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.
  • To post as a guest, your comment is unpublished.
    Nate Bee · 2 years ago
    Hello, this worked great for me until I tried to create dependent drop-down lists. I wanted my selection for my drop down list in cell C2, for example, to depend on what I selected from my drop down list in cell A2. When I use an index-match formula to create this dependent drop-down in C2 (c/p the formula into the data validation interface with "List" as my allowed value), my index-match formula becomes part of the drop down list's options. Any ideas?
    • To post as a guest, your comment is unpublished.
      Kim · 2 years ago
      Hi Nate, I'm having the same issue. Did you find a solution?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Nate Bee,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    sulabh chawla · 2 years ago
    works well for me. We all need to do data validation and create drop down first and then copy paste the macro. Thank you
  • To post as a guest, your comment is unpublished.
    Andrés Zapata · 2 years ago
    Good day, how to write words that are in the drop-down list, this option that you gave me serving me a lot, but additionally I would like to know how to write for example "ACETAMINO" 905701 ACETAMINOFEN AUTOMATED, and bring me all that word that contains my drop-down list
    With this option to consult the data requires me to start by typing in the order of the sentence, that is, having to write 905701 ... as I have a drop-down list of 1000 rows who consult the data we will have to memorize all the data and that would help.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Andrés Zapata · 2 years ago
    Buen día, como hacer para escribir palabras que esten en la lista desplegable, esta opción que ustedes me brindan me sirvió mucho, pero adicional me gustaría saber la forma de escribir por ejemplo "ACETAMINO" 905701 ACETAMINOFEN AUTOMATIZADO, y me traiga todo lo relacionado con esa palabra que contenga mi lista desplegable.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    victorjbm · 2 years ago
    Hi, bro. Thanks for the macro.
    I wanna know if you can edit this code to make autofill for emails.
    Ex: If my mail is victor@gmail.com and I write victor, and then @, when I write @ I got the common options (domains) for emails, like gmail.com, hotmail.com, outlook.com, etc.
    Is this possible?
    I would appreciate it very much.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      The code also works for email address. For the email address "victor@gmail.com", when typing "victor" or only the initial "v" into the cell, the entire email address "victor@gmail.com" will be atocomplete in the cell.
  • To post as a guest, your comment is unpublished.
    Will · 2 years 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?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Melinda · 2 years ago
    And of course as soon as I type the question, I figure out a fix. If I don't change the name of my combo box to TempCombo but leave it as ComboBox21 which it is the default name it starts with, the combo box seems to work perfectly.
  • To post as a guest, your comment is unpublished.
    Melinda · 2 years ago
    My combo box is only visible in Design Mode. When I close design mode, it disappears. Is there an easy fix for this?
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Melinda,
      In this case, the combo box works for the data validation drop down list.
      It only displays when selecting the cell that contains the drop down list.
  • To post as a guest, your comment is unpublished.
    Przem · 2 years ago
    Hi, thank you, great code. Is there a way to modify your code so that after typing "ana" I still will see "Nana" as a choice? Now it will narrow the choices to anything that starts with "ana". So now it is "ana*" but would be great if it could be "*ana*".
  • To post as a guest, your comment is unpublished.
    tkevink · 2 years ago
    How would I use this autocomplete feature and still being able to "tab"/"enter" to a new cell; and overwrite the already input information in the combo box?

    Right now I "tab"/"enter" and I need to delete the information in the cell in order to use the autocomplete again.
    • To post as a guest, your comment is unpublished.
      tkevink · 2 years ago
      Private Sub TempCombo_GotFocus()
      Me.TempCombo = Null
      End Sub



      I added this code and it allows me to delete what is in the cell. How would I just be able to overwrite what is there without deleting it?
      • To post as a guest, your comment is unpublished.
        crystal · 2 years ago
        Good Day,
        Thank you for your comment.
        But I don't really understand your question.
        The code you provide can help to clear the combo box cell value automatically when reselecting it. What do you mean overwrite it? Why not reselect or retype the new value in the combo box cell manually?
  • To post as a guest, your comment is unpublished.
    Kevin · 2 years ago
    How would I use this autocomplete feature and still being able to "tab"/"enter" to a new cell; and overwrite the already input information in the combo box?

    Right now I "tab"/"enter" and I need to delete the information in the cell in order to use the autocomplete again.
  • To post as a guest, your comment is unpublished.
    Reid Nickerson · 2 years ago
    Wow, that was EXACTLY what I was looking to do. Thanks so much for making it very straightforward and easy as pie.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Bala · 3 years ago
    where can i enter the value for the list? And when i use data validation, i select the list from another sheet and based upon the selection, Vlookup fills up other cells? How can i do the same with combo box? Please explain
    • To post as a guest, your comment is unpublished.
      Bala · 3 years ago
      It works now. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Eve · 3 years ago
    Thank you soooo much. you've saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    C.G. · 3 years ago
    Thanks for all great tips! The code isn't working when applied to a drop down list in Hebrew. Would you be able to help me with this? Thanks again!
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Thank you for your comment.
      Sorry can't help you with that.
  • To post as a guest, your comment is unpublished.
    Pine · 3 years ago
    HI I like the code very much. But I was using named ranged as the Source (i.e. Source: =itemlist) for data validation and it works properly before I insert the VBA code. However after I insert the VBA code into my worksheet, my drop-down list shown only 1 selection i.e. 'itemlist' in the Source. I know it works well if I use excel cells e.g. A1:A16 as the Source when setting up data validation, but I was prefer for using named ranged as the Source.

    Is there any solutions? Thanks.
    • To post as a guest, your comment is unpublished.
      Boot Dat · 2 years ago
      Im having the exact same problem as you are facing, and i cant find a way to fix it. have you found a solution for it yet ?
  • To post as a guest, your comment is unpublished.
    alrik.yeep@gmail.com · 3 years ago
    Hi thanks a lot for the code, but the drop-down listing only appears for the data validation lists where the "Source" comes from reference to excel cells (e.g. cell B3:B10), for those where the "Source" listing is text-based (e.g. "Yes,No") the combo box will fail to show the list of options available although a manual input can still be done.

    Can you help out on this issue? Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good day,
      Thanks for your comment. The code has been updated in the post, please have a try.
  • To post as a guest, your comment is unpublished.
    jennifer · 3 years ago
    when i go out of design mode my box disappears. also i dont see anywhere that you say to define or select the list?
  • To post as a guest, your comment is unpublished.
    Leonardo Ramos · 3 years ago
    Existe una forma para los formularios en word? Te lo agradecería muchísimo.
  • To post as a guest, your comment is unpublished.
    Harshit · 3 years ago
    How to skip blanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Harshit,
      All blanks need to be excluded while creating the drop down list.
      • To post as a guest, your comment is unpublished.
        gk · 3 years ago
        how do i change the source data for the list please?
  • To post as a guest, your comment is unpublished.
    James Trogdon · 3 years ago
    Thanks for this great code. I do have a question about if it would be possible to change the color of the linked cell if the person chooses a value from the list or if they type one of their own? For example, if I choose a value from the list the linked cell would show green text when I left the cell. If I typed my own value, then the linked cell would show red indicating I didn't choose one of the values from the list. Is this possible?
  • To post as a guest, your comment is unpublished.
    deepak_fer · 3 years ago
    Thanks for the wonderful code.
    I have a question.
    My cells in excel are of the nature x4x - y4y. The answers from the drop down are filtered only for the words matching the first half or starting with the alphabet xx but if i search for y4y, it will not show in the results.
    Is there a way to include the second half in the search as well?
    Also
    How can i modify the code so that the results shows all the alphabets from the search menu?
    Ex: If am searching for the word "example", but i input "ample", I would like to have the word "example" shown in the list as it contains the part of the search request.
  • To post as a guest, your comment is unpublished.
    lluis · 3 years ago
    thanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution? Thanks in advance. ;)
  • To post as a guest, your comment is unpublished.
    Lluis · 3 years ago
    thanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution. Thanks in advance. ;)
  • To post as a guest, your comment is unpublished.
    Derek · 3 years ago
    When I copy the VBA code into Visal Basic I cannot use copy paste anymore. I have to start Excel in normal mode to be able to copy,. How can I solve this?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Derek,
      The copy and paste functions work well in my case while using the code. Can you tell me which Office version you are using?
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Joe C. · 3 years ago
    Hello.
    How is this used for data entry? The primary reason to use data validation is to regulate your data input to have normalized results. I can think of how to use this for a search box, but not for what I would expect data validation to accomplish.
    Is there a way to put your data from the box into a new row?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Joe,
      Thanks for your comment.
      If you want to create a search box, please browse the below article to find the solution.

      How to create your own search box in Excel?
      https://www.extendoffice.com/documents/excel/4137-excel-create-a-search-box.html
  • To post as a guest, your comment is unpublished.
    Cwrivers · 3 years ago
    I have been using this code for months and love it, however I would like to use an if statement in my data validation source. I have the formula and it works without this vba code, but when I put the vba code back into the workbook the combo box doesn't show any values, just one blank box. Is there a way to incorporate an if statement for which list the code will look at.


    Example of my formula with bad formatting.
    =if(A1="x",named_range1,if(A1="y",named_range2))

    Thanks in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi,
      Would you mind sending me your workbook? My email address: zxm@addin99.com.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    munira · 3 years ago
    Thank you i have found out solution on your page after lots of trouble...you made it simple
    next challenge is to how to apply this to multiple cells?
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good day,
      The code can also deal with multiple cells. Please have a try.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Marc · 3 years ago
    MERCI Infiniment, cela à règler beaucoup de cas semblable pour moi MERCI encore
  • To post as a guest, your comment is unpublished.
    Glen · 3 years ago
    Hi

    Thanks this worked for me...I used a Named Range in a table so had a bit of a hiccup but found this youtube video to help out https://www.youtube.com/watch?v=JwA2gAbEXic&feature=youtu.be

    I was curious to know why in your code that you made reference to the Combo

    Set xCombox = xWs.OLEObjects("TempCombo")


    But you then also just used Me.TempCombo.DropDown ? Was there a reason you just didnt use Me.TempCombo??
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Glen,
      In this case, we are using combo box to assist the auto-complete operation in data validation drop-down lists which already created in the worksheet. So I use the TempCombo.DropDown instead of TempCombo.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    leducyvan@gmail.com · 3 years ago
    Thanks for the code it work's great the only thing i seem to have a long list of blank space after my list is there a way to fix it so only my list is in the selection box
    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good day,
      Thanks for your comment. However, I didn't find any blank space after my list. It is possible that the drop-down list you have created including blank cells?
  • To post as a guest, your comment is unpublished.
    cchambers · 3 years ago
    I have 2 drop down lists I would like to convert to combo boxes, the second list values are dependent on the option selected in list 1. Further, i have 2 additional copies of this model, and would like each of the drop downs to work separately, to allow the user to select items to compare between each model. Is there a way to do this? When i try the steps outlined, each of the combo boxes are linked to the same list.
    • To post as a guest, your comment is unpublished.
      aandrea · 3 years ago
      Did you find a solution?
  • To post as a guest, your comment is unpublished.
    Jordi · 3 years ago
    Now its only searching for the first letters. Is it possible that it also search for complete words in the middle. Example PEFC Thermopal white. If i write white that it search for every row with white in it. It's the same question what @Rusty asked below ''I too would love something like Kumar indicates. Let's say one of the values in the drop down list is "John Goodman", is there anyway for the combo box to select and populate "John Goodman" as the user types just "Goodman"?
  • To post as a guest, your comment is unpublished.
    Ilze · 3 years ago
    Is it possible to assign the combo box to a specific data validation list using this code? I have more than 1 data validation list, but I only want the combobox to run with 1 specific data validation list. Would appreciate your help with this.
  • To post as a guest, your comment is unpublished.
    Dhold7327 · 3 years ago
    This doesn't seem to work if your data validation source is a name range within a table. Is there any way around that?
    • To post as a guest, your comment is unpublished.
      M. Amir Ashraf · 3 years ago
      "This doesn't seem to work if your data validation source is a name range within a table", I've also encountered the same problem when assigning the range thru VBA, however, it does seem to work if you assign it manually thru properties. It is annoying, but is a way out.
  • To post as a guest, your comment is unpublished.
    sompadlik · 3 years ago
    Hi, please help me to sort my issue with this code. Its working fine excpet one thing. When code is active excel wont let me to Copy and paste anything within the sheet. I tested on 2016 excel and its PERFECT! but at work we have 2007 excel... Its working but blocking copy and paste, why it is happening?
  • To post as a guest, your comment is unpublished.
    Hossam · 3 years ago
    Hi,

    This is a great macro, it worked with me fine, but how do I make the selection limited to the drop down list, I tried typing a random name which was not n the list and it got accepted.

    Thanks
    • To post as a guest, your comment is unpublished.
      J Hames · 2 years ago
      Having this same issue. Would love to know if you ever found a solution. Using this code for a database at my place of business. Used by several different employees with the idea of keeping down mistakes made through typos.
  • To post as a guest, your comment is unpublished.
    makosipper@gmail.com · 3 years ago
    Doesn't work with data validation for me. It seems to be focused on manually input drop down lists, not drop down generated when you use Data Validation.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Good Day,
      It is a data validation drop down list provided in my case.
  • To post as a guest, your comment is unpublished.
    Carsten · 3 years ago
    OK got this to work with one problem, 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.

    I think it is something in the properties or VBA but not sure

    I also want to go to right cell after I hit enter as this is how I have it set up


    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Carsten,
      Please change the Style field to 2- fmSpecialEffectSunken in the Properties window of the Combo box, and then apply below VBA code. Hope I can help.

      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
  • To post as a guest, your comment is unpublished.
    Leandro · 3 years ago
    Hay alguna forma de mover el Combo? Como se encuentra directamente abajo de la lista desplegable al apretar Enter (una vez seleccionado el dato de dicha lista) te selecciona el Combo y termina mostrándote dos listas.