Log in  \/ 
x
or
x
x
Register  \/ 
x

or

Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 12.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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...


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 the full feature 30-day free trail of Kutools for Excel now!


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.


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
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.
    Jonas · 19 days ago
    How can I Use Tab/Arrows/Enter to move to another cell once the current cell is filled? Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 17 days ago
      Hi Jonas,
      The Tab key and the Enter key can work normally. But can't help with the Arrows yet. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Ramitha · 21 days ago
    Hi . It is a very useful code . However , I cannot use it on horizontal tables which are is a reference for vertical drop downs. Is there a solution ?
  • To post as a guest, your comment is unpublished.
    K1000z · 1 months ago
    Hi Guys, are you able to share a code which i can use so that the autocomplete is used on the same column i.e H in every sheet within the workbook.
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi,
      If you only want the column H to be affected, please try the below VBA code. But it is only available in one worksheet. For multiple sheets, you need to add the code to each sheet code window. Hope I can help.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 2019/8/14
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr
      Dim xRgStr As String
      Dim xRg As Range
      xRgStr = "H:H"

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      Set xRg = Intersect(Range(xRgStr), Target)
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      If .ListFillRange = "" Then
      xArr = Split(xStr, ",")
      Me.TempCombo.List = xArr
      End If
      .LinkedCell = Target.Address
      End With

      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
      Case 9
      Application.ActiveCell.Offset(0, 1).Activate
      Case 13
      Application.ActiveCell.Offset(1, 0).Activate
      End Select
      End Sub
      • To post as a guest, your comment is unpublished.
        K1000z · 1 months ago
        Looks good.Thanks Crystal.
  • To post as a guest, your comment is unpublished.
    Billy · 1 months ago
    I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Billy,
      Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Angga S.P. · 1 months ago
    Woooooooowwwwww very useful, thankyou for sharing, god bless you
  • To post as a guest, your comment is unpublished.
    Big.Moe · 1 months ago
    Love this! Thanks so much. Got a small issue though. I like to tab from one cell to the next, but when I get to the cell with the drop down list, I can tab into the cell with no problem, but once I'm in, it won't let me tab back out. I have to physically move the mouse over and click into the next cell. Any fix for this? Kind of disrupts my rhythm, which is why I loved this autocomplete function in the first place. ;o)
  • To post as a guest, your comment is unpublished.
    Jerome · 1 months ago
    This is a great find, however, I have one little issue: I like to tab through the cells as I am entering data. I can tab into the cell with the drop down menu just fine, but once I'm in that cell, it stops me from exiting the cell with a tab. I have to physically move the mouse to the next cell and click on it. Is there a fix to be able to simply tab in and tab out of the autocomplete cell? Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Jerome,
      The Tab key still works well in my case after applying the code. Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Alex C · 2 months ago
    I am using this code and a data validation with drop down lists for easy acces to what i need because i need the data to remain in excel boxes for other formulas to use it. However one of the combo boxes has almost 1500 items in the drop down and I'd like the user to be able to start typing a word in the combo box and get a list of suggestions to choose from based on the word entered for example if they type 'joh' in the combo box I want to show them a list of all the choices in the drop down that contains 'joh' so they can select the one they want. Now i can find the first 'joh' but it shows me like a list with what is near this 'joh', not specific all that contain 'joh'. Can i modify this code so can be used for what i want? Thank you and please help
  • To post as a guest, your comment is unpublished.
    Don P. · 2 months ago
    The Combobox works fine when 1-MatchEntryComplete is selected ,when I changed the selection to 2-fmMatchEntryNone in the Combobox properties it did not work ,i.e It does not auto select as I start typing. Is there a fix for this?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Don P.
      Only the 1-MatchEntryComplete option can make it work. Sorry can't help you for that.
  • To post as a guest, your comment is unpublished.
    Christian · 2 months ago
    It works. Thank you and God bless
  • To post as a guest, your comment is unpublished.
    Kuku · 3 months ago
    The code working but whenever I click on cell there the drop list auto prompt ,this make me unable to copy the contents and paste somewhere , how can I enable the copy and paste ?
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Kuku,
      When the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Charlis · 3 months ago
    I try copy the whole cell of droplisting after all selection , but whenever I click on cell it auto show the listing , this has cost me unable to copy the cells and paste the contents somewhere , any solutions , pls help ?
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Charlis,
      Actually when the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Eze · 3 months ago
    Hi! Does Anyone Know, how use this function just on a cell?
    • To post as a guest, your comment is unpublished.
      Thomas · 2 months ago
      Change line 16 to read "If ActiveCell.Address = "{ADDRESS e.g. $C$8}" Then"
  • To post as a guest, your comment is unpublished.
    ron · 4 months ago
    hi! I have tried the code, and it works fine, but it will not allow a macro to copy and paste.
  • To post as a guest, your comment is unpublished.
    RyanPlanteNJ · 4 months ago
    Hey guys, I'm having an issue where it doesn't let me type what I want to search for letter by letter. It just shows the whole word automatically and highlighted, and I start to type it moves it to the next closest word. is there anyway to not have it highlight the whole thing?
  • To post as a guest, your comment is unpublished.
    MartinK. · 4 months ago
    Great tutorial. Thank you.
  • To post as a guest, your comment is unpublished.
    me · 6 months ago
    Doesn't work. Please update.
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      Sorry for the inconvenience. Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Nadine · 6 months ago
    hi when i use the VBA code on a column that has dates it changes it to a number. can anyone help
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi Nadine,
      The problem didn't cause by the code.
      You need to format the column cells to date format before or after creating the drop-down list.
  • To post as a guest, your comment is unpublished.
    kennethaugng@gmail.com · 6 months ago
    Hi, Thaks for the code. Could I check if this formula can include an ignore blank function? This code took away my ignore blank function for my data validation. Please advise thanks
  • To post as a guest, your comment is unpublished.
    Ben · 7 months ago
    Hey, thanks for this! I have managed to get it to work, but like in Cartson's comment: I need to keep selections to the list. It populates ok, but if I enter a word not in the list, it still accepts it. The VBA code you replied with doesn't work unfortunately


    Like in Data Validation/locked cell, can it create some error or not allow the typed entry not in the list?


    Any help is much appreciated
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Ben,
      Sorry there are mistakes in the previous reply.
      After inserting the combo box, open its Properties window, change the Name to TempCombo, and then select 2 - fmStyleDropDownList from the Style field, and finally apply the below code. From now on, it won't allow entering word not in the list.

      Dim xRg As Range
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim I As Long
      Dim xWs As Worksheet
      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      Set xRg = Target
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      .LinkedCell = Target.Address
      End With
      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Dim xSel As Range
      On Error Resume Next
      Select Case KeyCode
      Case 13
      xRg.Offset(0, 1).Select
      End Select
      End Sub
      • To post as a guest, your comment is unpublished.
        Jonas · 19 days ago
        It doesnt work for me either, I can still enter items that are not on the list. can you please help!
      • To post as a guest, your comment is unpublished.
        Rodrigo Fernandez La · 2 months ago
        Hello Crystal,
        I have tried to do this... but it doesn't work... I have 4 lists 2 independent and the other two depend on others... It only works with the first list
  • To post as a guest, your comment is unpublished.
    defrancodominic@gmail.com · 7 months ago
    Really great tutorial, thanks. Do you know how I can adapt this code to get it to work for merged cells? Thanks again.
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi,
      The code does not work for merged cells yet. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Rob K · 8 months ago
    Hi every one i noticed a few questions asking if there is a way to limit this to one column but i didn't notice any answers, was this ever figured out? i have multiple drop downs in my work sheet and they are all being affected by the macro where i only want column H to be affected. Any help with this would be greatly appreciated.
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Rob K,
      The below VBA code can help you solve the problem, please have a try and thank you for your comment.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 2019/8/14
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr
      Dim xRgStr As String
      Dim xRg As Range
      xRgStr = "H:H"

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      Set xRg = Intersect(Range(xRgStr), Target)
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      If .ListFillRange = "" Then
      xArr = Split(xStr, ",")
      Me.TempCombo.List = xArr
      End If
      .LinkedCell = Target.Address
      End With

      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
      Case 9
      Application.ActiveCell.Offset(0, 1).Activate
      Case 13
      Application.ActiveCell.Offset(1, 0).Activate
      End Select
      End Sub
      • To post as a guest, your comment is unpublished.
        Lucas Sienk · 7 months ago
        Thank you for this fix! One quick question: I running this code across multiple worksheets (31 to be exact) but it is only affecting one. I have the code the same for each worksheet limiting the columns but it doesn't seem to be working on the other sheets. Thanks in advance!
        • To post as a guest, your comment is unpublished.
          crystal · 7 months ago
          Hi,
          The above code can only work on one sheet at a time. Sorry for the inconvenience.
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Rob K,
      The below VBA code can help you solve the problem. Thank you for your comemnt.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 20190812
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr
      Dim xRgStr As String
      Dim xRg As Range
      xRgStr = "H:H" 'The range of cells containg drop-down lists you will make them autocomplete

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      Set xRg = Intersect(Range(xRgStr), Target)
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      If .ListFillRange = "" Then
      xArr = Split(xStr, ",")
      Me.TempCombo.List = xArr
      End If
      .LinkedCell = Target.Address
      End With

      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
      Case 9
      Application.ActiveCell.Offset(0, 1).Activate
      Case 13
      Application.ActiveCell.Offset(1, 0).Activate
      End Select
      End Sub
  • To post as a guest, your comment is unpublished.
    Denise · 8 months ago
    When I paste the VBA code, do I need to change the Worksheet code editor to the Worksheet and Before Double Click? I changed those 2 fields and it populated codes into the code editor screen. So where do I paste the VBA code? after those? Before those? do I delete those 2 codes that autofill and then paste? I tried that but then the Worksheet changed back to General and Declarations and the code doesn't make my cells autofill. What am I doing wrong???
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Denise,
      Just right click the sheet tab (the worksheet contains the drop-down list you will make it autocomplete) and select View Code from the context menu, when the code editor opening, paste the code directly into it.
  • To post as a guest, your comment is unpublished.
    Chandan Mehta · 8 months ago
    How can i use same list for another combo box, do i need to write same code for another combo box, as i require 10 combo box
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Chandan Mehta,
      You just need to create 10 data validation drop-down lists, and then follow the steps in the article to achieve it (only need one combo box in the whole operation).
  • To post as a guest, your comment is unpublished.
    Ayelet · 8 months ago
    Is there a way to make this searchable (so that it returns anything from the list that matches anywhere in text not just the beginning)?
  • To post as a guest, your comment is unpublished.
    RK · 9 months ago
    How can I set it to accept only the text in the list?
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi RK,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Anthony · 10 months ago
    How can the code be modified such that pressing SHIFT + TAB moves the cursor left in the row?
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Hi Anthony,
      Sorry can't help you with that. Thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Anthony · 9 months ago
        No problem. I figured it out. Thanks, Crystal.
        • To post as a guest, your comment is unpublished.
          noob · 8 months ago
          Yeah want to know too
        • To post as a guest, your comment is unpublished.
          eeframe27 · 8 months ago
          Anthony, how did you end up doing this?
  • To post as a guest, your comment is unpublished.
    Parth · 1 years ago
    But what if i have dynamic list ? How can i provide the input to the combobox and refresh everytime the data is been added?
    • To post as a guest, your comment is unpublished.
      Conor · 9 months ago
      You'll have to make a table of the dynamic dataset and assign it to the 'ListFillRange' within the properties of your combobox.
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Hi Parth,
      Sorry can’t help you with this. Welcome to post any question about Excel to our forum: https://www.extendoffice.com/forum.html. You will get more Excel supports from our professional or other Excel fans.
  • To post as a guest, your comment is unpublished.
    MR Excel · 1 years ago
    Anyone looking for a simple dropdown input form in excell based on a list; i used Data.. "Data Validation" and then choose Allow: "List" and pointed the source at the list.
    This does not do the auto complete but does avoid any macros or dev and just uses native simple Excel features.
    • To post as a guest, your comment is unpublished.
      crystal · 11 months ago
      Good day,
      The dropdown list mentioned in the post is the Data Validation dropdown list.
      After creating the Data Validation dropdown list in the worksheet, go ahead to apply the steps from 2 to 10 in the post.
  • To post as a guest, your comment is unpublished.
    Fabian · 1 years ago
    Buenas tardes dentro de esa lista predictiva tengo campos de lista con la función indirecto pero me elimina , como puedo conservar las funciones de lista conservando la búsqueda predictiva.
  • To post as a guest, your comment is unpublished.
    Nate Bee · 1 years ago
    Hello, this worked great for me until I tried to create dependent drop-down lists. I wanted my selection for my drop down list in cell C2, for example, to depend on what I selected from my drop down list in cell A2. When I use an index-match formula to create this dependent drop-down in C2 (c/p the formula into the data validation interface with "List" as my allowed value), my index-match formula becomes part of the drop down list's options. Any ideas?
    • To post as a guest, your comment is unpublished.
      Kim · 11 months ago
      Hi Nate, I'm having the same issue. Did you find a solution?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Nate Bee,
      Sorry can't help you with that. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    sulabh chawla · 1 years ago
    works well for me. We all need to do data validation and create drop down first and then copy paste the macro. Thank you
  • To post as a guest, your comment is unpublished.
    Andrés Zapata · 1 years ago
    Good day, how to write words that are in the drop-down list, this option that you gave me serving me a lot, but additionally I would like to know how to write for example "ACETAMINO" 905701 ACETAMINOFEN AUTOMATED, and bring me all that word that contains my drop-down list
    With this option to consult the data requires me to start by typing in the order of the sentence, that is, having to write 905701 ... as I have a drop-down list of 1000 rows who consult the data we will have to memorize all the data and that would help.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Andrés Zapata · 1 years ago
    Buen día, como hacer para escribir palabras que esten en la lista desplegable, esta opción que ustedes me brindan me sirvió mucho, pero adicional me gustaría saber la forma de escribir por ejemplo "ACETAMINO" 905701 ACETAMINOFEN AUTOMATIZADO, y me traiga todo lo relacionado con esa palabra que contenga mi lista desplegable.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    victorjbm · 1 years ago
    Hi, bro. Thanks for the macro.
    I wanna know if you can edit this code to make autofill for emails.
    Ex: If my mail is victor@gmail.com and I write victor, and then @, when I write @ I got the common options (domains) for emails, like gmail.com, hotmail.com, outlook.com, etc.
    Is this possible?
    I would appreciate it very much.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      The code also works for email address. For the email address "victor@gmail.com", when typing "victor" or only the initial "v" into the cell, the entire email address "victor@gmail.com" will be atocomplete in the cell.
  • To post as a guest, your comment is unpublished.
    Will · 1 years ago
    When I applied this code to my worksheet, the Undo function is disabled (but only for this sheet in the workbook) -- is there a way to fix this so that undo can still be used?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Melinda · 1 years ago
    And of course as soon as I type the question, I figure out a fix. If I don't change the name of my combo box to TempCombo but leave it as ComboBox21 which it is the default name it starts with, the combo box seems to work perfectly.
  • To post as a guest, your comment is unpublished.
    Melinda · 1 years ago
    My combo box is only visible in Design Mode. When I close design mode, it disappears. Is there an easy fix for this?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Melinda,
      In this case, the combo box works for the data validation drop down list.
      It only displays when selecting the cell that contains the drop down list.
  • To post as a guest, your comment is unpublished.
    Przem · 1 years ago
    Hi, thank you, great code. Is there a way to modify your code so that after typing "ana" I still will see "Nana" as a choice? Now it will narrow the choices to anything that starts with "ana". So now it is "ana*" but would be great if it could be "*ana*".
  • To post as a guest, your comment is unpublished.
    tkevink · 1 years ago
    How would I use this autocomplete feature and still being able to "tab"/"enter" to a new cell; and overwrite the already input information in the combo box?

    Right now I "tab"/"enter" and I need to delete the information in the cell in order to use the autocomplete again.
    • To post as a guest, your comment is unpublished.
      tkevink · 1 years ago
      Private Sub TempCombo_GotFocus()
      Me.TempCombo = Null
      End Sub



      I added this code and it allows me to delete what is in the cell. How would I just be able to overwrite what is there without deleting it?
      • To post as a guest, your comment is unpublished.
        crystal · 1 years ago
        Good Day,
        Thank you for your comment.
        But I don't really understand your question.
        The code you provide can help to clear the combo box cell value automatically when reselecting it. What do you mean overwrite it? Why not reselect or retype the new value in the combo box cell manually?
  • To post as a guest, your comment is unpublished.
    Kevin · 1 years ago
    How would I use this autocomplete feature and still being able to "tab"/"enter" to a new cell; and overwrite the already input information in the combo box?

    Right now I "tab"/"enter" and I need to delete the information in the cell in order to use the autocomplete again.
  • To post as a guest, your comment is unpublished.
    Reid Nickerson · 1 years ago
    Wow, that was EXACTLY what I was looking to do. Thanks so much for making it very straightforward and easy as pie.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Bala · 1 years ago
    where can i enter the value for the list? And when i use data validation, i select the list from another sheet and based upon the selection, Vlookup fills up other cells? How can i do the same with combo box? Please explain
    • To post as a guest, your comment is unpublished.
      Bala · 1 years ago
      It works now. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Eve · 1 years ago
    Thank you soooo much. you've saved me a lot of time!
  • To post as a guest, your comment is unpublished.
    C.G. · 1 years ago
    Thanks for all great tips! The code isn't working when applied to a drop down list in Hebrew. Would you be able to help me with this? Thanks again!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Thank you for your comment.
      Sorry can't help you with that.
  • To post as a guest, your comment is unpublished.
    Pine · 1 years ago
    HI I like the code very much. But I was using named ranged as the Source (i.e. Source: =itemlist) for data validation and it works properly before I insert the VBA code. However after I insert the VBA code into my worksheet, my drop-down list shown only 1 selection i.e. 'itemlist' in the Source. I know it works well if I use excel cells e.g. A1:A16 as the Source when setting up data validation, but I was prefer for using named ranged as the Source.

    Is there any solutions? Thanks.
    • To post as a guest, your comment is unpublished.
      Boot Dat · 1 years ago
      Im having the exact same problem as you are facing, and i cant find a way to fix it. have you found a solution for it yet ?
  • To post as a guest, your comment is unpublished.
    alrik.yeep@gmail.com · 1 years ago
    Hi thanks a lot for the code, but the drop-down listing only appears for the data validation lists where the "Source" comes from reference to excel cells (e.g. cell B3:B10), for those where the "Source" listing is text-based (e.g. "Yes,No") the combo box will fail to show the list of options available although a manual input can still be done.

    Can you help out on this issue? Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Thanks for your comment. The code has been updated in the post, please have a try.
  • To post as a guest, your comment is unpublished.
    jennifer · 1 years ago
    when i go out of design mode my box disappears. also i dont see anywhere that you say to define or select the list?
  • To post as a guest, your comment is unpublished.
    Leonardo Ramos · 1 years ago
    Existe una forma para los formularios en word? Te lo agradecería muchísimo.
  • To post as a guest, your comment is unpublished.
    Harshit · 1 years ago
    How to skip blanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Harshit,
      All blanks need to be excluded while creating the drop down list.
      • To post as a guest, your comment is unpublished.
        gk · 1 years ago
        how do i change the source data for the list please?

Feature Tutorials