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

or

Hur fyller jag i automatiskt när du skriver i rullgardinslistan i Excel?

Om du har en rullgardinsmeny för datavalidering med stora objekt, måste du bläddra upp och ner i listan bara för att hitta den rätta eller skriva hela ordet direkt i listrutan. Om det finns en metod för att automatiskt slutföra när du skriver den första bokstaven i rullgardinsmenyn blir allt enklare. Denna handledning ger en VBA-metod som hjälper dig att uppnå det.

Autoslutför när du skriver i rullgardinsmenyn med VBA-kod
Fler handledning för rullgardinsmenyn ...


Autoslutför när du skriver i rullgardinsmenyn med VBA-kod

Gör så här för att göra en rullgardinslista autofullständig efter att ha skrivit motsvarande bokstäver i cellen.

För det första måste du infoga en kombinationsruta i kalkylbladet och ändra dess egenskaper.

1. Öppna kalkylbladet som innehåller rullgardinsmenyn som du vill göra det autofullständigt.

2. Innan du sätter i en kombinationsruta måste du lägga till fliken Developer i Excel-bandet. Om fliken Utvecklare visas på ditt band, växla till steg 3. Gör annars så här: Klicka på Fil > Tillbehör att öppna Tillbehör fönster. I denna Excel-alternativ fönstret klickar Skräddarsy bandet i den vänstra rutan, kontrollera Utvecklare och klicka sedan på OK knapp. Se skärmdump:

3. klick Utvecklare > Insert > Combo Box (ActiveX Control).

4. Rita en kombinationsruta i det aktuella kalkylbladet. Högerklicka på den och välj sedan Fastigheter från högerklickmenyn.

5. I Fastigheter dialogrutan, ersätt originaltexten i (Namn) fält med TempCombo.

6. Stäng av Designläge genom att klicka Utvecklare > Designläge.

Tillämpa sedan nedanstående VBA-kod

7. Högerklicka på den aktuella arkfliken och klicka Visa kod från snabbmenyn. Se skärmdump:

8. I öppningen Microsoft Visual Basic för applikationer kopiera och klistra in nedanstående VBA-kod i kalkylbladets kodfönster.

VBA-kod: Autoslutför när du skriver i rullgardinsmenyn

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. Tryck andra + Q samtidigt för att stänga Microsoft Visual Basic-applikationer fönster.

Från och med nu, när du klickar på en rullgardinsmeny, kommer rullgardinslistan att fråga automatiskt. Du kan börja skriva in bokstaven för att göra motsvarande objekt automatiskt komplett i vald cell. Se skärmdump:

Notera: Den här koden fungerar inte för sammanslagna celler.

Skapa enkelt listrutan med kryssrutor i Excel:

Vårt Listruta med kryssrutor nytta av Kutools för Excel kan hjälpa dig att enkelt skapa rullgardinsmenyn med kryssrutor i ett angivet intervall, aktuellt kalkylblad, aktuell arbetsbok eller alla öppnade arbetsböcker baserat på dina behov.
Ladda ner och prova det nu! (30-dagars gratis spår)


Relaterade artiklar:

Hur skapar jag rullgardinslista med flera kryssrutor i Excel?
Många Excel-användare tenderar att skapa rullgardinslista med flera kryssrutor för att välja flera objekt från listan per gång. Du kan faktiskt inte skapa en lista med flera kryssrutor med datavalidering. I den här handledningen kommer vi att visa dig två metoder för att skapa rullgardinslista med flera kryssrutor i Excel. Denna handledning ger metoden för att lösa problemet.

Skapa rullgardinslista från en annan arbetsbok i Excel
Det är ganska enkelt att skapa en rullgardinslista för datavalidering bland kalkylblad i en arbetsbok. Men om listdata du behöver för datavalideringen hittar du i en annan arbetsbok, vad skulle du göra? I den här guiden lär du dig hur du skapar en drop-down-lista från en annan arbetsbok i Excel i detalj.

Skapa en sökbar rullgardinslista i Excel
För en rullgardinsmeny med många värden är det inte lätt att hitta en riktig. Tidigare har vi introducerat en metod för automatisk komplettering av rullgardinsmenyn när du anger den första bokstaven i rullgardinsmenyn. Förutom funktionen för autoslutförande kan du också göra listrutan sökbar för att förbättra arbetseffektiviteten för att hitta rätt värden i listrutan. För att göra rullgardinsmenyn sökbar, prova metoden i den här självstudien.

Fyll i andra celler automatiskt när du väljer värden i Excel-listrutan
Låt oss säga att du har skapat en rullgardinslista baserat på värdena i cellområdet B8: B14. När du väljer något värde i listrutan vill du att motsvarande värden i cellintervall C8: C14 fylls automatiskt i en vald cell. För att lösa problemet kommer metoderna i denna handledning att göra dig en tjänst.

Fler handledning för rullgardinsmenyn ...


De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50% och minskar hundratals musklick åt dig varje dag!
officetab botten
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.
    Mitch · 3 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 · 3 years ago
    Does it work in mac os?
  • To post as a guest, your comment is unpublished.
    pravitejakumar@gmail.com · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
      Good Day,
      Cannot hide the drop down when typed. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Adinda · 3 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 · 3 years ago
      Good Day,
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Paul · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
      Good day.
      Sorry I am not sure I got your question.
  • To post as a guest, your comment is unpublished.
    Nimal · 3 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 · 3 years ago
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Papa Shark · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
    Excel workbook crashes a lot.
  • To post as a guest, your comment is unpublished.
    James1985 · 3 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 · 3 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 · 3 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 · 3 years ago
          See attached screen-shot.
          • To post as a guest, your comment is unpublished.
            crystal · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
        What parameter do I change to switch to the double click?
  • To post as a guest, your comment is unpublished.
    Rob · 3 years ago
    I got a syntax error for "Dim xStr As String"
  • To post as a guest, your comment is unpublished.
    Stacey · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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.
  • To post as a guest, your comment is unpublished.
    jpbisani · 4 years ago
    Combo box makes selection(a1:a6000) correctly but does not filler as i type. Instead whatever i enter appears in E6. In combo box properties i have added in Linked cells $e$6:$e$100 and List full range: $a$1:$a$6000.
  • To post as a guest, your comment is unpublished.
    jpbisani · 4 years ago
    combo box selects everything I want (A1:a6000). The problem is it does not filter as i type in combo box. Instead whatever I type in combobox appears in e6. In tempbox properties I have added Linked cells $e$6:$e100 & list fill range $a$1:$a$6000. is where problem arise.
  • To post as a guest, your comment is unpublished.
    Dee · 4 years ago
    Hello,

    I tried to use it with my list but no list appears on my drop down list. help.
  • To post as a guest, your comment is unpublished.
    sasha · 4 years ago
    How do I do this for more than one drop down list?
    • To post as a guest, your comment is unpublished.
      Wilnex · 4 years ago
      It should work on all the drop down list you have in the worksheet where the combo box is.
  • To post as a guest, your comment is unpublished.
    James · 4 years ago
    Freaking Awesome.. thanks alot
  • To post as a guest, your comment is unpublished.
    MilkyTech · 4 years ago
    This works ok, however, definitely causes problems for the worksheet that contains the vba code. I don't have an issue with pasting as others do but "Undo" and "Redo" buttons don't function within this sheet (Ctrl+Z doesn't work either).
    Also, I would like to know if the "double-click" requirement can be worked around? I want to be able to just start typing in a "selected" cell rather than a "double-clicked" cell.
    One more issue is that this code turns every cell in the sheet into a combo box instead of just the dropdowns.
    • To post as a guest, your comment is unpublished.
      Hooly · 3 years ago
      Any work around on the 'selected' rather than 'double clicked'?
  • To post as a guest, your comment is unpublished.
    raj · 4 years ago
    but , how you make list for drop down from data ...
  • To post as a guest, your comment is unpublished.
    Jennifer · 4 years ago
    Well... it worked for one cell but when I tried to make it work for the whole column I couldn't :(

    What I have is
    Col A=Item name
    Col B= Date Created
    Col C= I want to enter Category but have dropdown list to choose from

    I have 723 "Items" so 723 rows where I want the dropdown list to appear as I go about entering the values.

    Any suggestions?
    Thank you in advance
  • To post as a guest, your comment is unpublished.
    Jennifer · 4 years ago
    Woohoo! Followed the steps in Autocomplete when typing in drop down list with VBA code
    and I've gotten exactly what I wanted to happen! Thank you! Thank you!
  • To post as a guest, your comment is unpublished.
    Zal · 4 years ago
    Hi!
    This is great. But how to I link my drop down list to the Combobox??? pls help.
    • To post as a guest, your comment is unpublished.
      Desh · 4 years ago
      Hello
      I have problem with this, i cant see any values on the drop down. Can anybody tell me how i can link my data range with the drop down? to show suggestions.
      if you can send me complected spread sheet that would be much appreciated.( My email is deshandsouza@gmail.com)
      • To post as a guest, your comment is unpublished.
        Desh · 4 years ago
        I got solution for the above! Just missed out to created Data validation drop down list. its works fine now.
  • To post as a guest, your comment is unpublished.
    Mike · 4 years ago
    Works Very nicely for drop downs using ='range' but functionality breaks as soon as the range is a result of a formula eg =IF(A1>0,'range','alternative').
    Able to type in an unpopulated TempCombo
  • To post as a guest, your comment is unpublished.
    Minhaj Ameen · 4 years ago
    Do not forget to add the range of items for dropdown in the ComboBox Property 'ListFillRange"
  • To post as a guest, your comment is unpublished.
    dddd · 4 years ago
    where is range of source list for validation?