Note: The other languages of the website are Google-translated. Back to English
Log på  \/ 
x
or
x
Tilmeld  \/ 
x

or

Hvordan autofuldføres, når du skriver i rullelisten i Excel?

For en rulleliste for datavalidering med masser af elementer skal du rulle op og ned i listen for at finde den, du har brug for, eller skrive hele ordet korrekt i listeboksen. Er der nogen metoder til at lave en automatisk udfyldt rulleliste, når du skriver det første bogstav eller nogen relative tegn? Det vil hjælpe folk med at arbejde mere effektivt i regneark med rullelister i celler. Denne vejledning giver dig to metoder til at hjælpe dig med at opnå det.

Autofuldfør, når du skriver i rullelisten med VBA-kode
Autofuldfør, når du skriver i rullelisten med et fantastisk værktøj

Flere vejledninger til rullelisten ...


Autofuldfør, når du skriver i rullelisten med VBA-kode

Gør som følger for at lave en rulleliste, der automatisk udfyldes, når du har skrevet de tilsvarende bogstaver i cellen.

For det første skal du indsætte en kombinationsboks i regnearket og ændre dens egenskaber.

1. Åbn regnearket, der indeholder rullelistecellen, du vil gøre det autofuldførelse.

2. Før du indsætter et kombinationsfelt, skal du tilføje fanen Udvikler til Excel-båndet. Hvis fanen Udvikler vises på dit bånd, skift til trin 3. Ellers gør du som følger: Klik på filet > Indstillinger at åbne Indstillinger vindue. Heri Excel-indstillinger vindue, skal du klikke på Tilpas bånd i venstre rude skal du kontrollere Udvikler og klik derefter på OK knap. Se skærmbillede:

3. klik Udvikler > indsatte > Kombinationsboks (ActiveX-kontrol).

4. Tegn en kombinationsboks i det aktuelle regneark. Højreklik på den, og vælg derefter Ejendomme fra højreklikmenuen.

5. i Ejendomme dialogboksen, skal du udskifte den originale tekst i (Navn) felt med TempCombo.

6. Sluk for Design Mode ved at klikke Udvikler > Designtilstand.

Anvend derefter nedenstående VBA-kode

7. Højreklik på fanen for det aktuelle ark, og klik Vis kode fra genvejsmenuen. Se skærmbillede:

8. I åbningen Microsoft Visual Basic til applikationer vindue, skal du kopiere og indsætte nedenstående VBA-kode i regnearkets kodevindue.

VBA-kode: Autofuldfør, når du skriver i rullelisten

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. Trykke andre + Q taster samtidigt for at lukke Microsoft Visual Basic-applikationer vindue.

Fra nu af, når du klikker på en rullelistecelle, vil rullelisten automatisk bede. Du kan begynde at indtaste brevet for at gøre det tilsvarende element automatisk komplet i den valgte celle. Se skærmbillede:

Bemærk: Denne kode fungerer ikke for flettede celler.


Autofuldfør, når du skriver i rullelisten med et fantastisk værktøj

Da VBA er svært at håndtere for mange Excel-brugere, anbefaler vi her Søgbar rulleliste træk ved Kutools til Excel for at hjælpe dig med nemt at udfylde en rulleliste automatisk, når du skriver det første tegn.

Bemærk: Før du anvender funktionen, skal du sikre dig, at du har oprettet rullelister i regnearket. I dette tilfælde placeres rullelisterne i D3:D9.

1. klik Kutools > Rulleliste > Søgbar rulleliste > Indstillinger.

2. I popping up Søgbar rulleliste dialogboksen, skal du foretage følgende indstillinger.

2.1) I Anvend på sektion, angiv et område eller et regneark, hvor du vil anvende den søgbare rullelistefunktion;
Tips: Efter at have valgt Specificeret anvendelsesområde alternativknap, kan du vælge Nuværende regneark, Nuværende projektmappe or Alle projektmapper fra rullelisten efter dine behov.
2.2) I Indstillinger sektion, vælg Matcher kun begyndelsen af ​​ordet mulighed eller Case-følsom mulighed eller begge dele;
Matcher kun begyndelsen af ​​ordet: Marker denne indstilling, kun de elementer, der begynder med det indtastede tegn, vises, og det første matchede element vil automatisk blive fuldført i listeboksen. Fjern markeringen i denne indstilling for at få vist elementer, der indeholder det indtastede tegn.
Case-følsom: Marker denne indstilling for at udføre store og små bogstaver matchning med indtastningstegnene.
2.3) Klik OK.

3. Når du er færdig med indstillingerne, skal du aktivere funktionen ved at klikke Kutools > Rulleliste > Søgbar rulleliste > Aktiver søgbar rulleliste.

Når du nu klikker på rullelisten, vises en liste med alle elementer. Du behøver kun at skrive et tegn i tekstboksen for at få vist alle tilsvarende elementer, og bruge Up or ned pilen for at vælge det element, du har brug for, eller bare lad det første matchende element automatisk udfylde listeboksen, og tryk derefter på Indtast tasten for at autofuldføre rullelistens celle med det matchende element. Se nedenstående demo.

Klik for at vide mere om denne funktion.

  Hvis du vil have en gratis prøveperiode (30 dage) på dette værktøj, klik for at downloade det, og gå derefter til at anvende handlingen i henhold til ovenstående trin.


Relaterede artikler:

Hvordan oprettes rullelisten med flere afkrydsningsfelter i Excel?
Mange Excel-brugere har tendens til at oprette rulleliste med flere afkrydsningsfelter for at vælge flere emner fra listen pr. Gang. Faktisk kan du ikke oprette en liste med flere afkrydsningsfelter med datavalidering. I denne vejledning skal vi vise dig to metoder til at oprette rulleliste med flere afkrydsningsfelter i Excel. Denne vejledning giver metoden til at løse problemet.

Opret rullelisten fra en anden projektmappe i Excel
Det er ret nemt at oprette en rulleliste med datavalidering blandt regneark i en projektmappe. Men hvis de listedata, du har brug for til datavalidering, findes i en anden projektmappe, hvad ville du gøre? I denne vejledning lærer du, hvordan du opretter en drop fown-liste fra en anden projektmappe i Excel i detaljer.

Opret en søgbar rulleliste i Excel
For en rulleliste med mange værdier er det ikke let at finde en ordentlig. Tidligere har vi introduceret en metode til automatisk udfyldelse af rullelisten, når du indtaster det første bogstav i rullemenuen. Udover funktionen autofuldførelse kan du også gøre rullelisten søgbar for at forbedre arbejdseffektiviteten ved at finde korrekte værdier i rullelisten. For at gøre rullelisten søgbar, prøv metoden i denne vejledning.

Udfyld automatisk andre celler, når du vælger værdier i rullelisten i Excel
Lad os sige, at du har oprettet en rulleliste baseret på værdierne i celleområde B8: B14. Når du vælger en hvilken som helst værdi i rullelisten, ønsker du, at de tilsvarende værdier i celleområde C8: C14 automatisk udfyldes i en valgt celle. Til løsning af problemet vil metoderne i denne vejledning gøre dig en tjeneste.

Flere vejledninger til rullelisten ...


De bedste Office-produktivitetsværktøjer

Kutools til Excel løser de fleste af dine problemer og øger din produktivitet med 80%

  • Genbruge: Indsæt hurtigt komplekse formler, diagrammer og alt, hvad du har brugt før; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Super formel bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner uden at miste data; Split celler indhold; Kombiner duplikerede rækker / kolonner... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Mere end 300 kraftfulde funktioner. Understøtter Office / Excel 2007-2019 og 365. Understøtter alle sprog. Nem implementering i din virksomhed eller organisation. Fuld funktioner 30-dages gratis prøveperiode. 60-dages pengene tilbage garanti.
kte-fane 201905

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50%og reducerer hundredvis af museklik for dig hver dag!
officetab bund
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.
    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 · 4 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.
  • To post as a guest, your comment is unpublished.
    Martin Winlow · 4 years ago
    Hi,


    Could you please re-do this tutorial for Excel for Mac V15? MW
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Dear Martin Winlow,
      The code haven't been tested in Mac system.
  • To post as a guest, your comment is unpublished.
    lonercom · 4 years ago
    I am using this script which works well with text but not with numerals. Here are some screenshots; 1 is the code, 2 is working by alpha, 3 is not working by Number. Ultimately I would like to be able to search by name or number (Col A or Col C). To work around the separate column issue, I copied the same date and transposed the data in those cells.

    Any help would be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Robert,
      What do you mean "is not working by number"? The code works well with text and numerals in my case. And I saw the case in your picture 3, the related whole number 40726 is automatically populated when you type 407 into the list box. Please let me know if I missed something in the case.
      • To post as a guest, your comment is unpublished.
        lonercom · 4 years ago
        Yes, however the data associated with that number (as in photo 2) does not populate.
  • To post as a guest, your comment is unpublished.
    Brittany · 4 years ago
    Hello, Is there anyway to make the drop down menu only show results that match what you're typing? For example; I am using this to select items for an invoice template and I have a dew wines that start with 'Gaja'. As I type Gaja the top result shows in my list of 20 results but the other options are below it and I have to use the mouse to scroll down to those or type the name of the wine until it is the only option. I would like to type 'G' and then be only shown all items with G. Then type 'Ga' and only see items with GA and so on.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Brittany,
      Hope method in this article: How to create a searchable drop down list in Excel? can help you.
      Please follow the link: https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html
  • To post as a guest, your comment is unpublished.
    Pippa · 4 years ago
    I must be missing something but where do you specify what cell range actually compromises the dropdown list? I can do this using a combo box rather than activeX but can't get the autocomplete to work with a combo box.
    • To post as a guest, your comment is unpublished.
      Teddy · 4 years ago
      Pippa, i'm with you. I've followed the instructions but it does not work. Ive added the ListFIllRange & LinkedCell but it doesnt work so there is definitly something missing from these instructions
    • To post as a guest, your comment is unpublished.
      lonercom · 4 years ago
      .ListFillRange=
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Pippa,
      Sorry can't get your point.
  • To post as a guest, your comment is unpublished.
    Karan · 4 years ago
    Hi,

    My drop-down list has an custom format of mmm-yy. When I use the combo drop down it turns my entries i.e Jan-17 into number values . Applying a format doesn't fix that.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Karan,
      I tried exactly as you mentioned above, but it works well in my case. The combo box still keeps the date format.
      Which Office version do you use?
  • To post as a guest, your comment is unpublished.
    zikxxx · 4 years ago
    Hello,
    Is there any way to fill the list from a row ? When i try, only first entry is shown.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good Day,
      This problem cannot be solved.
      Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
      You will get more supports about Excel from our Excel professional.
  • To post as a guest, your comment is unpublished.
    zik · 4 years ago
    Hello,
    Is there any way to fill the list from a row ? when i try, only first entry is shown.
  • To post as a guest, your comment is unpublished.
    Mike · 4 years ago
    Greetings,

    when i try to use the code i get the message "Method or Data member not found". The highligted language is TempBombo in the Me.TempCombo.Dropdown line in the Worksheet_SelectionChange sub.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Mike,
      You need to change the name of the combo box to TempCombo in the Properties dialog box as we mentioned in above step 5.
  • To post as a guest, your comment is unpublished.
    arotolo · 4 years ago
    Is there a VBA code for this to work with merged cells?
    • To post as a guest, your comment is unpublished.
      arotolo · 4 years ago
      Surprisingly, the attached code actually is working on my merged cells!!! Great step by step. Thank you for putting this out here to help us!
  • To post as a guest, your comment is unpublished.
    Mitch · 4 years ago
    Hi Crystal,


    I'm having issues with the code when using a drop down for number.


    The Code works perfectly in cells where the data validations is names, but when I move to a cell where the validation is a number (that is part of a formula in another cell). Excel tells me that the number is stored as text and when I let excel change it to a number my formula in the other cell start working. Is this because of how the variable is define in vba? is there a work around for this?


    Thanks for you help,
    M
  • To post as a guest, your comment is unpublished.
    emerson · 4 years ago
    Does it work in mac os?
  • To post as a guest, your comment is unpublished.
    pravitejakumar@gmail.com · 4 years ago
    Hi,


    Thanks for your code.
    its is working fine.
    But here it is searching only the starting word, if i have the same word in the middle its not showing in the suggestions.


    please help
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good Day,
      What do you mean same word in the middle? Would you please provide a screenshot to show what you are exactly trying to do?
      • To post as a guest, your comment is unpublished.
        Rusty · 4 years ago
        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.
    Steven · 4 years ago
    Hello i followed your instructions, put the drop down into cell H4. once i have copied the code and gone back to excel the dropdown menu has disappeared, and the only way to retreive/see it is when in developer mode, in which case nothing appears. How do i select what info goes into the dropdown menu? etc. etc. These instructions are not clear whatsoever for someone who doesn't code - the reason i am actually looking at your page.


    Please provide assistance.


    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      The Design Mode under the Developer tab will help you find the inserted Combo Box in worksheet. Please remember the location (cell address) of the Combo Box, turn off the Design Mode, click on the cell contains the Combo Box, and you will get the dropdown menu immediately.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Steven,
      The inserted Combo Box will disappear as it is covered by the original data validation drop-down list you have created in your worksheet. And normally, the data validation drop-down list does not appear in your worksheet until you click on the cell which contains it. So you have to remember which cell contains the data validation drop-down list and then enable the autocomplete function.
  • To post as a guest, your comment is unpublished.
    SanketSharma · 4 years ago
    Hello. Thank you for the code. It worked fine initially, however now the data field is stuck of the initial entry I made. When I try to select another entry, the box does not update itself. I would really appreciate any help you can provide on this.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good Day,
      Sorry I did not get your point. Would you explain it clearly or provide a screenshot of what you are trying to do?
  • To post as a guest, your comment is unpublished.
    Basavaraju · 4 years ago
    Hi Wonderfull work! it helped me alot. Is there anyway possible for not to show the drop down when typed???. your reply much appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good Day,
      Cannot hide the drop down when typed. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Adinda · 4 years ago
    Please change the Step 2 which says "Outlook 2007" if it was a mistake as I believe. I worked so hard trying to find Excel Options in Outlook, thinking that it actually does exist T_______T

    Aside of that, instructions were very clear, and it works very well! Thank you very much!!!
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good Day,
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Paul · 4 years ago
    Works (almost) exactly as I would like! Thanks! However, I can tab forward from the new drop-down - but can't back-tab (shift+tab) to the previous column. Shift+tab works the same as regular tab. Any way to fix this?
  • To post as a guest, your comment is unpublished.
    jerry · 4 years ago
    where do you add the list to be populated in the combo box
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Jerry,
      The drop-down list has already existed in the worksheet before applying the above steps in this case.
  • To post as a guest, your comment is unpublished.
    Sebastian · 4 years ago
    Hi there! Excellent work. The only problem is that when i try to apply VLOOKUP; the value doesnt seem to "exist" and the function won't work. I have typed the data manually and it works ok, but whenever i use this script it'll show #N/A
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good day,
      Sorry I am not sure I got your question. Would be nice if you could provide a screenshot of your worsheet case as well as your VLOOKUP fuction.
      Thank you!
  • To post as a guest, your comment is unpublished.
    James Rock · 4 years ago
    Works great, however, can the VB code be modified to work with an INDIRECT data validation reference?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good day!
      The code can't work in that case. Sorry about that!
  • To post as a guest, your comment is unpublished.
    x · 4 years ago
    hi unfortunately this new drop down is unable to trigger a "Change Event" macro which my original drop down was supposed to do - any solutions?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Good day.
      Sorry I am not sure I got your question.
  • To post as a guest, your comment is unpublished.
    Nimal · 4 years ago
    Thank you, this works exactly as described! Am helping out a local food rescue org here with their excel.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Papa Shark · 4 years ago
    Your codes works as what I wanted, however it messed up with Undo function.

    Now I can't use undo function. Little help please.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Papa,
      The Undo function can't be restored until you breaking the VBA script. Can't fix it. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Dolphin · 4 years ago
    I managed to figure it out. Really cool. But it operates in only 1 sheet? how to apply it in the whole workbook?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Dolphin,
      For applying to the entire workbook in bulk, please try the below VBA code.

      After inserting a Combo Box (ActiveX Controls) into a worksheet containing drop-down list, please don't change any its properties as above method mentioned. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window, then paste the below VBA code into the ThisWorkbook code window. And finally press the Alt + Q keys to close the window.

      Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      Dim xStr As String
      Dim xCombox As OLEObject
      On Error Resume Next
      Set xCombox = Sh.OLEObjects("ComboBox1")
      If xCombox Is Nothing Then
      Set xCombox = Sh.OLEObjects.Add("Forms.ComboBox.1")
      End If
      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
      .LinkedCell = Target.Address
      End With
      xCombox.Activate
      xCombox.Object.DropDown
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    piech.mac@gmail.com · 4 years ago
    Thank you works great! I need `autofill combo box list` in sheet1 connected with data from sheet2. Is this possible?
    I have tried:
    ListFillRange = Sheet2.Range("A2:A30")
    but it doesnt work.
    May you help please?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Maciej,
      Please create your data validation drop-down list with data from sheet2 at first, and then do the above instruction step by step. Thank you.
  • To post as a guest, your comment is unpublished.
    Jacques Viau · 4 years ago
    Works well, however, when I double click the cell and type text that does NOT match my drop down, it accepts this text. I do not want it to accept any text other than the drop down text. My data validation is checked for matching text. If I do not double click the cell, it does not accept any text other than the drop down. What is happening? Please help!
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Jacques,
      I am so sorry to tell you that the problem you mentioned cannot be fixed. The Combo Box we used to acheive the auto-complete function allows users to type in text that not match in the list.
  • To post as a guest, your comment is unpublished.
    vic85.pham@gmail.com · 4 years ago
    4. Draw the combo box in current opened worksheet and right click it. Select Properties in the right-clicking menu ===> Mine doesnt show Properties. What is the error?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Chester,
      The ComboBox you added in your worksheet should be an ActiveX Control combo box. Please check for the proper Combo Box.
  • To post as a guest, your comment is unpublished.
    Karen Postell · 4 years ago
    This code works great for a drop down list for 1 row. I need drop downs for 400 rows. Have you tried this?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Karen,
      The code works well for all drop down lists in the specified worksheet. Please try it again as above method shown step by step.
      Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    Chloe · 4 years ago
    Hello. This works very well, thank you. Only issue now is - with the drop down boxes before it would not allow someone to enter in a value that was not in the list - and an error alert would appear. Now I have done the above the user is allowed to enter in a different value that is not in the list but I do not want that. Any tips? Thank you.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Sorry Chloe, can't fix this probem as the combo box does not have an error alert feature like data validation drop down list.
      Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    Andreas H · 4 years ago
    Hello. It works great for me except one thing that is when i want to scroll down in the droplist it work using the arrows, but when i press the "handle" and pull down.. the entire window goes blank. What to do about that? Sorry for my bad English. Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Andreas,
      The code work well for me without the problem you mentioned above. Can you test it again in a new workbok? Thank you for your comment!
      Best Regards, Crystal
      • To post as a guest, your comment is unpublished.
        Andreas · 4 years ago
        Hi. Can't get it to work. Can i perhaps mail you the sheet and you can have a quick look at it? Best regards Andreas
  • To post as a guest, your comment is unpublished.
    Bart Kean · 4 years ago
    Hello, I have a workbook with 120 sheets that I need to apply this to. Is there anyway to apply this formula to the entire workbook without having to apply this process to each sheet?

    Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Bart Kean,
      For applying to the entire workbook in bulk, please try the below VBA code.

      After inserting a Combo Box (ActiveX Controls) into a worksheet containing drop-down list, please don't change any its properties as above method mentioned. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window, then paste the below VBA code into the ThisWorkbook code window. And finally press the Alt + Q keys to close the window.

      Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

      Dim xStr As String

      Dim xCombox As OLEObject
      On Error Resume Next

      Set xCombox = Sh.OLEObjects("ComboBox1")

      If xCombox Is Nothing Then

      Set xCombox = Sh.OLEObjects.Add("Forms.ComboBox.1")
      End If

      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

      .LinkedCell = Target.Address

      End With

      xCombox.Activate

      xCombox.Object.DropDown

      End If

      End Sub


      Best regards, Crystal
  • To post as a guest, your comment is unpublished.
    akashsingh.1234@gmail.com · 4 years ago
    Hello Crystal


    My excel worksheet has started to crash a lot, after applying the code.
    I have Excel 2010.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Akash,

      The code works well in my Excel 2010. May be you can create a new workbook with the data you required and try the code again. Or would be nice if you cound send me your workbook through siluvia@extendoffice.com. Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    Akash · 4 years ago
    Excel workbook crashes a lot.
  • To post as a guest, your comment is unpublished.
    James1985 · 4 years ago
    Good Day,

    Looking for some help please :-)

    I have followed the steps outlined above and have gotten as far as step 10 although, as per the attached screen-shot, I seem to be having an issue with the VBA code that was copied at step 8.

    Is there something that I have done wrong when following any of the previous steps or when copying the code?

    Thanks in advance for any help and assistance :-)
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear James,
      Would you like to provide more clear information of your issue with a screen-shot? Any error reminder? Or your Office version?
      Thank you for your comment!
      • To post as a guest, your comment is unpublished.
        James1985 · 4 years ago
        Hi Crystal,

        Thanks a lot for your reply. Okay, I can see that the VBA code listed in step 8 above was updated earlier by someone :-) now when I complete steps 8 & 9 I have success at step 10 with the new drop-down box being added to my worksheet which includes the auto-complete functionality.

        The issue that I have now is that the original drop-down list / filter option that was added to my worksheet is still visible below the new auto-complete field that was added in step 4 (see attached screen-shot). Is there any way to hide the original drop-down list as this is confusing for users of my spreadhsheet?

        FYI - I am using Office / Excel 2016.

        Best Regards, James
        • To post as a guest, your comment is unpublished.
          James1985 · 4 years ago
          See attached screen-shot.
          • To post as a guest, your comment is unpublished.
            crystal · 4 years ago
            Dear James,
            Very glad to receive your reply. I didn't see your attached screenshot, but i understand you issue now (see the screenshot below).
            The code has been updated again. After the whole operation, the original drop-down list will be hidden automatically when click on it.
            Please let me know if the code works for you!
            Thanks again!

            Best Regards, Crystal
            • To post as a guest, your comment is unpublished.
              James1985 · 4 years ago
              Hi Crystal,

              That's great! Confirm that the updated code worked perfectly and the original drop-down list is now hidden as required.

              Thank you so much for your help :-)

              Best Regards, James
  • To post as a guest, your comment is unpublished.
    wendyt · 4 years ago
    Hi, I have tried it, it works. However I would like to have an auto update list and auto complete. When I tried it, it does not work. The drop down list is empty, when I use the autoupdate OFFSET COUNTA formula. Can you help me on it? Thanks.
    • To post as a guest, your comment is unpublished.
      perry · 4 years ago
      I'm Having the same issue. any chance you figured out solution? I have Tried using the formulas "=INDIRECT()" and "IFS()" as the source but cant her it to work. the list comes up empty.
  • To post as a guest, your comment is unpublished.
    Bian · 4 years ago
    Is there anyway to change from double click to selected cell? Double clicking each cell can be time consuming
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Bian,
      We have updated the code already. It now supports one click to activate the drop-down list.
      Thank you for your comment!
      • To post as a guest, your comment is unpublished.
        Connor · 4 years ago
        What parameter do I change to switch to the double click?
  • To post as a guest, your comment is unpublished.
    Rob · 4 years ago
    I got a syntax error for "Dim xStr As String"
  • To post as a guest, your comment is unpublished.
    Stacey · 4 years ago
    Code worked great! However, I'd like to copy the ComboBox in several cells. I did so, and every time I select something from the dropdown - it changes ALL of the other combo boxes to the same selection!? How do I remedy that?
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Stacey,
      If you want to copy the ComboBox in several cells, please insert Data Validation drop-down lists into current worksheet one by one with content you need after finish the above steps.
      Then the new inserted drop-down lists will be changed to ComboBoxes automatically when selecting. And selections in different drop-down lists will be individual.
      Thank you for your comment!
  • To post as a guest, your comment is unpublished.
    Boy · 4 years ago
    Can assist on this please? All of column has combobox now where I intend my worksheet to only have 2 columns.
    Also how can I make this code works for the entire workbook and not only for one worksheet?
    Please help.
  • To post as a guest, your comment is unpublished.
    Vikas · 4 years ago
    Hello, thank you so much for this... But how can we run this on protected sheet also i don't want every cell work as combo box on double click...... Pl. Help
  • To post as a guest, your comment is unpublished.
    John P · 4 years ago
    Is there an easy/efficient way to handle it if you have to do lookups from different/dynamic sources?

    Right now, I have a workbook for recording stats for a dart league with one page per match. Each page has the home and away teams and I use data validation/list to create drop-downs. I'm wondering if it is possible to do lookups that use named ranges (the team names/Away/Home). I'm using Excel 2016; you'd think by now that this would be incorporated into the product.
  • To post as a guest, your comment is unpublished.
    Sandra · 4 years ago
    I also need the solution for limiting the combo box to only one column of the worksheet.
    Thank you. :-)
  • To post as a guest, your comment is unpublished.
    Bonnie Denham · 4 years ago
    I also want to know how to just start typing as opposed to having to first double-click in the cell for it to auto-populate.
  • To post as a guest, your comment is unpublished.
    Torti · 4 years ago
    Is it possible to change the code that not every cell is an Combox?
    I just want it on one Coloumn.
    Thanks
  • To post as a guest, your comment is unpublished.
    Thorsten Rausch · 4 years ago
    Hello, How to change the code that it just work an one Columbus?
  • To post as a guest, your comment is unpublished.
    Thorsten Rausch · 4 years ago
    Hey, is it possible to change the vba code that it doesn't work on the complete worksheet. i need it for one Columbus.