How to autocomplete when typing in Excel drop down list?

If you have a data validation drop down list with large items, you need to scroll up and down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial will provide a VBA method to help you achieve it.

Autocomplete when typing in drop down list with VBA code
More tutorials for drop down list...


Autocomplete when typing in drop down list with VBA code

Please do as follows to make a drop down list autocomplete after typing corresponding letters in the cell.

Firstly, you need to insert a combo box into the worksheet and change its properties.

1. Open the worksheet that contains the drop down list cell you want to make it autocomplete.

2. Before inserting a Combo box, you need to add the Developer tab to the Excel ribbon. If the Developer tab is showing on your ribbon, shift to step 3. Otherwise, do as follows: Click File > Options to open the Options window. In this Excel Options window, click Customize Ribbon in the left pane, check the Developer box, and then click the OK button. See screenshot:

3. Click Developer > Insert > Combo Box (ActiveX Control).

4. Draw a combo box in current worksheet. Right click it and then select Properties from the right-clicking menu.

5. In the Properties dialog box, please replace the original text in the (Name) field with TempCombo.

6. Turn off the Design Mode by clicking Developer > Design Mode.

Then, apply the below VBA code

7. Right click on current sheet tab and click View Code from the context menu. See screenshot:

8. In the opening Microsoft Visual Basic for Applications window, please copy and paste the below VBA code into the worksheet’s Code window.

VBA code: Autocomplete when typing in drop down list

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. Press Alt + Q keys simultaneously to close the Microsoft Visual Basic Applications window.

From now on, when click on a drop down list cell, the drop down list will prompt automatically. You can start to type in the letter to make the corresponding item complete automatically in selected cell. See screenshot:

Note: This code does not work for merged cells.

Easily create drop-down list with checkboxes in Excel:

The Drop-down List with Check Boxes utility of Kutools for Excel can help you easily create drop-down list with checkboxes in a specified range, current worksheet, current workbook or all opened workbooks based on your needs.
Download and try it now! ( 30-day free trail)


Related articles:

How to create drop down list with multiple checkboxes in Excel?
Many Excel users tend to create drop down list with multiple checkboxes in order to select multiple items from the list per time. Actually, you can’t create a list with multiple checkboxes with Data Validation. In this tutorial, we are going to show you two methods to create drop down list with multiple checkboxes in Excel. This tutorial provides the method to solve the problem.

Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.

Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.

Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.

More tutorials for drop down list...


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    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 · 2 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
    How do I do this for more than one drop down list?
    • To post as a guest, your comment is unpublished.
      Wilnex · 3 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 · 3 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?
  • To post as a guest, your comment is unpublished.
    Dante · 4 years ago
    I have completed the autocomplete drop down list, however, i do not know how to show the values after typing the text in the drop down list. For example, in my DATA BASE SHEET, Column 1 consist of the items and column 2 & 3 consist the cost of the item. By typing the item in my drop down list IN THE MAIN SHEET in column 1, the cost of the items appears in column 2 & 3. Is it possible that it can be done? Help me please