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

or

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


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.
    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?
  • To post as a guest, your comment is unpublished.
    James Trogdon · 1 years ago
    Thanks for this great code. I do have a question about if it would be possible to change the color of the linked cell if the person chooses a value from the list or if they type one of their own? For example, if I choose a value from the list the linked cell would show green text when I left the cell. If I typed my own value, then the linked cell would show red indicating I didn't choose one of the values from the list. Is this possible?
  • To post as a guest, your comment is unpublished.
    deepak_fer · 1 years ago
    Thanks for the wonderful code.
    I have a question.
    My cells in excel are of the nature x4x - y4y. The answers from the drop down are filtered only for the words matching the first half or starting with the alphabet xx but if i search for y4y, it will not show in the results.
    Is there a way to include the second half in the search as well?
    Also
    How can i modify the code so that the results shows all the alphabets from the search menu?
    Ex: If am searching for the word "example", but i input "ample", I would like to have the word "example" shown in the list as it contains the part of the search request.
  • To post as a guest, your comment is unpublished.
    lluis · 1 years ago
    thanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution? Thanks in advance. ;)
  • To post as a guest, your comment is unpublished.
    Lluis · 1 years ago
    thanks for the code, but it only works for me in the first list, I have some inderect lists after the first that don't show any value. Is there any solution. Thanks in advance. ;)
  • To post as a guest, your comment is unpublished.
    Derek · 1 years ago
    When I copy the VBA code into Visal Basic I cannot use copy paste anymore. I have to start Excel in normal mode to be able to copy,. How can I solve this?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Derek,
      The copy and paste functions work well in my case while using the code. Can you tell me which Office version you are using?
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Joe C. · 1 years ago
    Hello.
    How is this used for data entry? The primary reason to use data validation is to regulate your data input to have normalized results. I can think of how to use this for a search box, but not for what I would expect data validation to accomplish.
    Is there a way to put your data from the box into a new row?
  • To post as a guest, your comment is unpublished.
    Cwrivers · 1 years ago
    I have been using this code for months and love it, however I would like to use an if statement in my data validation source. I have the formula and it works without this vba code, but when I put the vba code back into the workbook the combo box doesn't show any values, just one blank box. Is there a way to incorporate an if statement for which list the code will look at.


    Example of my formula with bad formatting.
    =if(A1="x",named_range1,if(A1="y",named_range2))

    Thanks in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Would you mind sending me your workbook? My email address: zxm@addin99.com.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    munira · 1 years ago
    Thank you i have found out solution on your page after lots of trouble...you made it simple
    next challenge is to how to apply this to multiple cells?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      The code can also deal with multiple cells. Please have a try.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Marc · 2 years ago
    MERCI Infiniment, cela à règler beaucoup de cas semblable pour moi MERCI encore
  • To post as a guest, your comment is unpublished.
    Glen · 2 years ago
    Hi

    Thanks this worked for me...I used a Named Range in a table so had a bit of a hiccup but found this youtube video to help out https://www.youtube.com/watch?v=JwA2gAbEXic&feature=youtu.be

    I was curious to know why in your code that you made reference to the Combo

    Set xCombox = xWs.OLEObjects("TempCombo")


    But you then also just used Me.TempCombo.DropDown ? Was there a reason you just didnt use Me.TempCombo??
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Glen,
      In this case, we are using combo box to assist the auto-complete operation in data validation drop-down lists which already created in the worksheet. So I use the TempCombo.DropDown instead of TempCombo.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    leducyvan@gmail.com · 2 years ago
    Thanks for the code it work's great the only thing i seem to have a long list of blank space after my list is there a way to fix it so only my list is in the selection box
    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Thanks for your comment. However, I didn't find any blank space after my list. It is possible that the drop-down list you have created including blank cells?
  • To post as a guest, your comment is unpublished.
    cchambers · 2 years ago
    I have 2 drop down lists I would like to convert to combo boxes, the second list values are dependent on the option selected in list 1. Further, i have 2 additional copies of this model, and would like each of the drop downs to work separately, to allow the user to select items to compare between each model. Is there a way to do this? When i try the steps outlined, each of the combo boxes are linked to the same list.
    • To post as a guest, your comment is unpublished.
      aandrea · 2 years ago
      Did you find a solution?
  • To post as a guest, your comment is unpublished.
    Jordi · 2 years ago
    Now its only searching for the first letters. Is it possible that it also search for complete words in the middle. Example PEFC Thermopal white. If i write white that it search for every row with white in it. It's the same question what @Rusty asked below ''I too would love something like Kumar indicates. Let's say one of the values in the drop down list is "John Goodman", is there anyway for the combo box to select and populate "John Goodman" as the user types just "Goodman"?
  • To post as a guest, your comment is unpublished.
    Ilze · 2 years ago
    Is it possible to assign the combo box to a specific data validation list using this code? I have more than 1 data validation list, but I only want the combobox to run with 1 specific data validation list. Would appreciate your help with this.
  • To post as a guest, your comment is unpublished.
    Dhold7327 · 2 years ago
    This doesn't seem to work if your data validation source is a name range within a table. Is there any way around that?
    • To post as a guest, your comment is unpublished.
      M. Amir Ashraf · 2 years ago
      "This doesn't seem to work if your data validation source is a name range within a table", I've also encountered the same problem when assigning the range thru VBA, however, it does seem to work if you assign it manually thru properties. It is annoying, but is a way out.
  • To post as a guest, your comment is unpublished.
    sompadlik · 2 years ago
    Hi, please help me to sort my issue with this code. Its working fine excpet one thing. When code is active excel wont let me to Copy and paste anything within the sheet. I tested on 2016 excel and its PERFECT! but at work we have 2007 excel... Its working but blocking copy and paste, why it is happening?
  • To post as a guest, your comment is unpublished.
    Hossam · 2 years ago
    Hi,

    This is a great macro, it worked with me fine, but how do I make the selection limited to the drop down list, I tried typing a random name which was not n the list and it got accepted.

    Thanks
    • To post as a guest, your comment is unpublished.
      J Hames · 1 years ago
      Having this same issue. Would love to know if you ever found a solution. Using this code for a database at my place of business. Used by several different employees with the idea of keeping down mistakes made through typos.
  • To post as a guest, your comment is unpublished.
    makosipper@gmail.com · 2 years ago
    Doesn't work with data validation for me. It seems to be focused on manually input drop down lists, not drop down generated when you use Data Validation.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good Day,
      It is a data validation drop down list provided in my case.
  • To post as a guest, your comment is unpublished.
    Carsten · 2 years ago
    OK got this to work with one problem, I need to keep selections to the list. It populates ok, but if I enter a word not in the list, it still accepts it.

    I think it is something in the properties or VBA but not sure

    I also want to go to right cell after I hit enter as this is how I have it set up


    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Carsten,
      Please change the Style field to 2- fmSpecialEffectSunken in the Properties window of the Combo box, and then apply below VBA code. Hope I can help.

      Dim xRg As Range
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim I As Long
      Dim xWs As Worksheet
      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      Set xRg = Target
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      .LinkedCell = Target.Address
      End With
      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Dim xSel As Range
      On Error Resume Next
      Select Case KeyCode
      Case 13
      xRg.Offset(0, 1).Select
      End Select
      End Sub
  • To post as a guest, your comment is unpublished.
    Leandro · 2 years ago
    Hay alguna forma de mover el Combo? Como se encuentra directamente abajo de la lista desplegable al apretar Enter (una vez seleccionado el dato de dicha lista) te selecciona el Combo y termina mostrándote dos listas.
  • To post as a guest, your comment is unpublished.
    Alicia · 2 years ago
    This code seems to work sometimes for me - but not consistently. I have a document with multiple lists and it will auto populate most of the time but not all of the time. It seems to have a problem auto populating when there was no information in the cell prior - however data validation extends the entire column. Please advise a fix.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Alicia
      The code works well in my case. After auto populating, you need to clear the selection in the drop-down list to activate the next auto populate operation.
  • To post as a guest, your comment is unpublished.
    Martin Winlow · 2 years ago
    Hi,


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

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

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

    when i try to use the code i get the message "Method or Data member not found". The highligted language is TempBombo in the Me.TempCombo.Dropdown line in the Worksheet_SelectionChange sub.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Mike,
      You need to change the name of the combo box to TempCombo in the Properties dialog box as we mentioned in above step 5.
  • To post as a guest, your comment is unpublished.
    arotolo · 2 years ago
    Is there a VBA code for this to work with merged cells?
    • To post as a guest, your comment is unpublished.
      arotolo · 2 years ago
      Surprisingly, the attached code actually is working on my merged cells!!! Great step by step. Thank you for putting this out here to help us!
  • To post as a guest, your comment is unpublished.
    Mitch · 2 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 · 2 years ago
    Does it work in mac os?
  • To post as a guest, your comment is unpublished.
    pravitejakumar@gmail.com · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 years ago
      Good Day,
      Cannot hide the drop down when typed. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Adinda · 2 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 · 2 years ago
      Good Day,
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Paul · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 years ago
      Good day.
      Sorry I am not sure I got your question.
  • To post as a guest, your comment is unpublished.
    Nimal · 2 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 · 2 years ago
      I’m glad I could help.
  • To post as a guest, your comment is unpublished.
    Papa Shark · 2 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 · 2 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 · 2 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 · 2 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