Note: The other languages of the website are Google-translated. Back to English
Zaloguj Się  \/ 
x
or
x
Rejestruję się  \/ 
x

or

Jak autouzupełnianie podczas pisania w rozwijanej liście programu Excel?

Jeśli masz listę rozwijaną do sprawdzania poprawności danych z dużymi elementami, musisz przewijać listę w górę iw dół, aby znaleźć właściwą, lub wpisać całe słowo bezpośrednio w polu listy. Jeśli istnieje metoda pozwalająca na automatyczne uzupełnianie podczas wpisywania pierwszej litery z rozwijanej listy, wszystko stanie się łatwiejsze. W tym samouczku znajdziesz metodę VBA, która pomoże Ci to osiągnąć.

Autouzupełnianie podczas wpisywania listy rozwijanej z kodem VBA
Więcej samouczków dla rozwijanej listy ...


Autouzupełnianie podczas wpisywania listy rozwijanej z kodem VBA

Wykonaj następujące czynności, aby lista rozwijana była automatycznie uzupełniana po wpisaniu odpowiednich liter w komórce.

Po pierwsze, musisz wstawić pole kombi do arkusza i zmienić jego właściwości.

1. Otwórz arkusz zawierający rozwijaną listę komórek, dla których chcesz, aby były one automatycznie uzupełniane.

2. Przed wstawieniem pola kombi należy dodać kartę Deweloper do wstążki programu Excel. Jeśli karta Deweloper jest wyświetlana na wstążce, przejdź do kroku 3. W przeciwnym razie wykonaj następujące czynności: Kliknij filet > Opcje otworzyć Opcje okno. W tym Opcje programu Excel okno, kliknij Dostosuj Wstążkę w lewym okienku zaznacz plik Deweloper a następnie kliknij OK przycisk. Zobacz zrzut ekranu:

3. kliknij Deweloper > wstawka > Pole kombi (formant ActiveX).

4. Narysuj pole kombi w bieżącym arkuszu. Kliknij go prawym przyciskiem myszy, a następnie wybierz Właściwości z menu po kliknięciu prawym przyciskiem myszy.

5. w Właściwości W oknie dialogowym zamień oryginalny tekst w (Nazwa) pole z TempCombo.

6. Wyłącz Tryb projektowania klikając przycisk Deweloper > Tryb projektowania.

Następnie zastosuj poniższy kod VBA

7. Kliknij prawym przyciskiem myszy bieżącą kartę arkusza i kliknij Wyświetl kod z menu kontekstowego. Zobacz zrzut ekranu:

8. W otwarciu Microsoft Visual Basic for Applications należy skopiować i wkleić poniższy kod VBA do okna kodu arkusza roboczego.

Kod VBA: autouzupełnianie podczas pisania na rozwijanej liście

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. naciśnij inny + Q klawisze jednocześnie, aby zamknąć Aplikacje Microsoft Visual Basic okno.

Odtąd po kliknięciu komórki listy rozwijanej, lista rozwijana wyświetli się automatycznie. Możesz zacząć wpisywać literę, aby odpowiadająca jej pozycja była automatycznie uzupełniana w wybranej komórce. Zobacz zrzut ekranu:

Uwaga: Ten kod nie działa w przypadku scalonych komórek.

Z łatwością utwórz listę rozwijaną z polami wyboru w programie Excel:

Pandemia Lista rozwijana z polami wyboru użyteczność Kutools dla programu Excel może pomóc w łatwym utworzeniu listy rozwijanej z polami wyboru w określonym zakresie, bieżącym arkuszu, bieżącym skoroszycie lub wszystkich otwartych skoroszytach w zależności od potrzeb.
Pobierz i wypróbuj teraz! ( 30-dniowy darmowy szlak)


Podobne artykuły:

Jak utworzyć listę rozwijaną z wieloma polami wyboru w programie Excel?
Wielu użytkowników programu Excel ma tendencję do tworzenia listy rozwijanej z wieloma polami wyboru, aby wybrać wiele pozycji z listy na raz. W rzeczywistości nie można utworzyć listy z wieloma polami wyboru z funkcją Walidacja danych. W tym samouczku pokażemy dwie metody tworzenia rozwijanej listy z wieloma polami wyboru w programie Excel. W tym samouczku przedstawiono metodę rozwiązania problemu.

Utwórz listę rozwijaną z innego skoroszytu w programie Excel
Utworzenie listy rozwijanej sprawdzania poprawności danych w arkuszach roboczych w skoroszycie jest dość łatwe. Ale jeśli lista danych potrzebnych do weryfikacji danych znajduje się w innym skoroszycie, co byś zrobił? W tym samouczku dowiesz się, jak szczegółowo utworzyć listę rozwijaną z innego skoroszytu w programie Excel.

Utwórz listę rozwijaną z możliwością wyszukiwania w programie Excel
W przypadku listy rozwijanej z wieloma wartościami znalezienie odpowiedniej nie jest łatwą pracą. Wcześniej wprowadziliśmy metodę automatycznego uzupełniania listy rozwijanej po wpisaniu pierwszej litery w rozwijanym polu. Oprócz funkcji autouzupełniania, możesz również umożliwić przeszukiwanie listy rozwijanej, aby zwiększyć wydajność pracy w znajdowaniu odpowiednich wartości na liście rozwijanej. Aby umożliwić przeszukiwanie listy rozwijanej, wypróbuj metodę opisaną w tym samouczku.

Automatycznie wypełniaj inne komórki podczas wybierania wartości na liście rozwijanej programu Excel
Załóżmy, że utworzyłeś listę rozwijaną na podstawie wartości w zakresie komórek B8: B14. Po wybraniu dowolnej wartości z listy rozwijanej chcesz, aby odpowiednie wartości w zakresie komórek C8: C14 były automatycznie wypełniane w wybranej komórce. Aby rozwiązać problem, skorzystaj z metod przedstawionych w tym samouczku.

Więcej samouczków dla rozwijanej listy ...


Najlepsze narzędzia biurowe

Kutools for Excel rozwiązuje większość twoich problemów i zwiększa wydajność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2019 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowy bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i zmniejsza setki kliknięć myszą każdego dnia!
officetab dół
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.
    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 · 7 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 · 2 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.
  • To post as a guest, your comment is unpublished.
    Alicia · 3 years ago
    This code seems to work sometimes for me - but not consistently. I have a document with multiple lists and it will auto populate most of the time but not all of the time. It seems to have a problem auto populating when there was no information in the cell prior - however data validation extends the entire column. Please advise a fix.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Alicia
      The code works well in my case. After auto populating, you need to clear the selection in the drop-down list to activate the next auto populate operation.