Suggerimento: le altre lingue sono tradotte da Google. Puoi visitare il English versione di questo link.
Connetti
x
or
x
x
Registrati
x

or

Come eseguire il completamento automatico quando si digita nell'elenco a discesa Excel?

Se si dispone di un elenco a discesa di convalida dei dati con valori grandi, è necessario scorrere verso il basso nell'elenco solo per trovare quello corretto oppure digitare direttamente l'intera parola nella casella di riepilogo. Se esiste un metodo per consentire il completamento automatico quando si digita la prima lettera nell'elenco a discesa, tutto diventerà più semplice. In questo tutorial, ti forniremo l'inserimento di una casella combinata nel foglio di lavoro e l'esecuzione del codice VBA insieme per ottenere la funzione di completamento automatico nell'elenco a discesa.

Completamento automatico quando si digita nell'elenco a discesa con codice VBA


Combina più fogli di lavoro / cartelle di lavoro in un foglio di lavoro / cartella di lavoro:

Combinare più fogli di lavoro o cartelle di lavoro in un unico foglio di lavoro o cartella di lavoro può essere un compito enorme nel tuo lavoro quotidiano. Ma se lo hai Kutools for Excel, la sua potente utilità - Combinare può aiutarti a combinare rapidamente più fogli di lavoro, cartelle di lavoro in un foglio di lavoro o in una cartella di lavoro. Scarica ora la traccia completa di Xutox-day di Kutools per Excel!

Kutools for Excel: con più di 200 pratici componenti aggiuntivi di Excel, liberi di provare senza limitazioni nei giorni 60. Scarica la versione di prova gratuita ora!

Scheda Office Abilita la modifica e la navigazione a schede in Office e semplifica notevolmente il tuo lavoro ...
Kutools per Excel risolve la maggior parte dei problemi e aumenta la produttività del 80%
  • Riutilizzare qualsiasi cosa: Aggiungi le formule più utilizzate o complesse, i grafici e qualsiasi altra cosa ai tuoi preferiti e riutilizzali rapidamente in futuro.
  • Più delle funzioni di testo di 20: Estrai numero da stringa di testo; Estrai o rimuovi parte di testi; Converti numeri e valute in parole inglesi ...
  • Unisci strumenti: Più cartelle di lavoro e fogli in uno; Unisci più celle / righe / colonne senza perdere dati; Unisci righe duplicate e somma ...
  • Strumenti di divisione: Suddivisione dei dati in più fogli in base al valore; Una cartella di lavoro per più file Excel, PDF o CSV; Una colonna a più colonne ...
  • Incolla Saltare Righe nascoste / filtrate; Conte E Somma per colore di sfondo; Crea una mailing list e Invia e-mail in base al valore della cella...
  • Super filtro: Crea schemi di filtro avanzati e applica a tutti i fogli; Ordinare per settimana, giorno, frequenza e altro; Filtro da grassetto, formule, commento ...
  • Oltre alle potenti funzionalità di 300; Funziona con Office 2007-2019 e 365; Supporta tutte le lingue; Facile implementazione nella tua azienda o organizzazione.

Completamento automatico quando si digita nell'elenco a discesa con codice VBA

Innanzitutto è necessario inserire una casella combinata nel foglio di lavoro e modificarne le proprietà, quindi eseguire il codice VBA per abilitare il completamento automatico.

1. Entra nel foglio di lavoro che contiene l'elenco a discesa che desideri venga completato automaticamente.

2. Prima di inserire la casella combinata, è necessario abilitare il Sviluppatore scheda nel nastro.

1). In Excel 2010 e 2013, fare clic su Compila il > Opzioni. E nella Opzioni finestra di dialogo, fare clic Personalizzazione barra multifunzione nel pannello di destra, controlla il Sviluppatore casella, quindi fare clic su OK pulsante. Vedi screenshot:

2). In Outlook 2007, fare clic su Office pulsante> Opzioni di Excel. Nel Opzioni di Excel finestra di dialogo, fare clic Popolare nella barra destra, quindi controllare il Mostra la scheda Sviluppatorenel nastro casella e infine fare clic su OK pulsante.

3. Quindi fare clic Sviluppatore > inserire > Casella combinata per Controlli ActiveX. Vedi screenshot:

4. Disegna la casella combinata nel foglio di lavoro aperto corrente e fai clic con il pulsante destro del mouse. Selezionare Proprietà nel menu di scelta rapida.

5. Nel Proprietà finestra di dialogo, è necessario:

1). Cambia il nome in TempCombo Il Nome campo;

2). Specifica il tipo di carattere che ti serve in fonte campo;

3). Scorri verso il basso per selezionare 1-fmMatchEntryComplete Il MatchEntry campo;

4). Chiudi il Proprietà la finestra di dialogo.

6. Disattiva la modalità di progettazione con un clic Sviluppatore > Modalità di progettazione.

7. Fare clic con il tasto destro sulla scheda del foglio di lavoro aperto e fare clic Visualizza codice. Vedi screenshot:

8. Assicurarsi che sia aperto l'editor del codice del foglio di lavoro corrente, quindi copiare e incollare il codice VBA sottostante. Vedi screenshot:

Codice VBA: completamento automatico quando si digita nell'elenco a discesa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
    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. Clic Compila il > Chiudi e torna a Microsoft Excel chiudere il Microsoft Visual Basic per applicazione finestra.

10. Ora, basta fare clic sulla cella con l'elenco a discesa, è possibile visualizzare l'elenco a discesa viene visualizzato come una casella combinata, quindi digitare la prima lettera nella casella, la parola corrispondente verrà completata automaticamente. Vedi screenshot:

Note: Questo codice VBA non è applicato alle celle unite.

Office Tab - Navigazione a schede, modifica e gestione delle cartelle di lavoro in Excel:

Office Tab offre un'interfaccia a schede come mostrato nei browser Web come Google Chrome, le nuove versioni di Internet Explorer e Firefox in Microsoft Excel. esso volere essere uno strumento che fa risparmiare tempo e che è irrealizzabile nel tuo lavoro. Vedi sotto demo:

Clicca per la prova gratuita di Office Tab!

Scheda Office per Excel

Articoli correlati:


Kutools per Excel risolve la maggior parte dei problemi e aumenta la produttività del 80%

  • Riutilizzo: Inserire rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Cifra le celle con password; Crea mailing list e invia email ...
  • Super Formula Bar (modifica facilmente più righe di testo e formula); Layout di lettura (leggere e modificare facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere i dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Previeni celle duplicate; Confronta le gamme...
  • Seleziona Duplica o Unica Righe; Seleziona righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molti libri di esercizi; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento alla formula; Creazione automatica di riferimenti a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • estrarre il testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare subtotali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtro ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale di grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle basate su colonne chiave; Dividi i dati in più fogli; Converti in batch xls, xlsx e PDF...
  • Oltre alle potenti funzionalità di 300. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile implementazione nella tua azienda o organizzazione. Funzioni complete di prova gratuita di 30-day.
kte tab 201905

Scheda Office Porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Editore, accesso, Visio e progetto.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la tua produttività di 50% e riduce centinaia di clic del mouse per te ogni giorno!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    me · 11 days ago
    Doesn't work. Please update.
  • To post as a guest, your comment is unpublished.
    Nadine · 19 days ago
    hi when i use the VBA code on a column that has dates it changes it to a number. can anyone help
  • To post as a guest, your comment is unpublished.
    Kenneth Augustine Ng · 1 months 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
  • To post as a guest, your comment is unpublished.
    Ben · 2 months 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
    • To post as a guest, your comment is unpublished.
      crystal · 1 months 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
  • To post as a guest, your comment is unpublished.
    Dominic De Franco · 2 months 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 · 1 months 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 months 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 months 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.
        Lucas Sienk · 1 months 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 · 1 months 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 months 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 months 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 months 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 months 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 months 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 months 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 · 4 months ago
    How can I set it to accept only the text in the list?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi RK,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Anthony · 5 months 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 · 4 months ago
      Hi Anthony,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Parth · 6 months 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 · 4 months 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 · 6 months 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 · 7 months 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 · 6 months 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 · 7 months 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 · 7 months 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 · 5 months ago
      Hi Nate, I'm having the same issue. Did you find a solution?
    • To post as a guest, your comment is unpublished.
      crystal · 7 months 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 · 8 months 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 · 8 months 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 · 8 months 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 · 8 months 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 · 8 months ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Victor Betancurt · 9 months 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 · 8 months 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 · 9 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?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months 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 · 9 months 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 · 9 months 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 · 8 months 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 · 9 months 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.
    K. K. · 9 months 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.
      K. K. · 9 months 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 · 8 months 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 · 9 months 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 · 10 months 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.
    Bala · 1 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.
    Eve · 1 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. · 1 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 · 8 months ago
      Hi,
      Thank you for your comment.
      Sorry can't help you with that.
  • To post as a guest, your comment is unpublished.
    Pine Corn · 1 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 · 11 months 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.
    shade131ful · 1 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 · 1 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 · 1 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 · 1 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 · 1 years ago
    How to skip blanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 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 · 1 years ago
        how do i change the source data for the list please?
  • To post as a guest, your comment is unpublished.
    James Trogdon · 1 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 John Fernandes · 1 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 · 1 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 · 1 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 · 1 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 · 1 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. · 1 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.
    Christopher Rivers · 1 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 · 1 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 · 1 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 · 1 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 · 1 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 · 1 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 · 1 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.
    Yvan · 1 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 · 1 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 · 1 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.
    Jordi · 1 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 · 1 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.
    Dan · 1 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 · 1 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.
    imre · 1 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?