Excel data validation with suggestions autocomplete

Question

Apologies for my low level of Excel understanding  maybe what I am looking to do is not possible     I have a list of 120 entries that I want to use as data validation  But instead of people having to scroll down the 120 options it would be great if they can start typing and see the options that could match what they are willing to write  Kind of like in autocomplete  I have found  this tip   I would like to have more options when the typing starts   For example this would be a part of the list     Awareness   Education   Budget   Budget Planning   Enterprise Budget    When typing  B  I would like the three last options to appear and to click one of them  I don t know if there is a way of include vlookup in here     Ideally the perfect thing would be to have several  tags  in one cell  but I fear that is absolutely impossible with excel   Thank you very much for any input and sorry if I haven t explained myself properly

User · Answer

I adapted the answer by ChrisB  Like in his example a temporary combobox is made visible when a cell is clicked  Additionally    List of Combobox items is updated as user types  only matching items are displayed if any item from combobox is selected  filtering is skipped as it makes sense and because of this error    x000D   x000D  Option Explicit x000D   x000D  Private Const DATA RANGE    A1 A16  x000D  Private Const DROPDOWN RANGE    F2 F10  x000D  Private Const HELP COLUMN     G  x000D   x000D   x000D  Private Sub Worksheet SelectionChange ByVal target As Range  x000D      Dim xWs As Worksheet x000D      Set xWs   Application ActiveSheet x000D       x000D      On Error Resume Next x000D       x000D      With Me TempCombo x000D           LinkedCell   vbNullString x000D           Visible   False x000D      End With x000D       x000D      If target Cells count  gt  1 Then x000D          Exit Sub x000D      End If x000D       x000D      Dim isect As Range x000D      Set isect   Application Intersect target  Range DROPDOWN RANGE   x000D      If isect Is Nothing Then x000D         Exit Sub x000D      End If x000D          x000D      With Me TempCombo x000D           Visible   True x000D           Left   target Left - 1 x000D           Top   target Top - 1 x000D           Width   target Width   5 x000D           Height   target Height   5 x000D           LinkedCell   target Address x000D   x000D      End With x000D   x000D      Me TempCombo Activate x000D      Me TempCombo DropDown x000D  End Sub x000D   x000D  Private Sub TempCombo Change   x000D      If Me TempCombo Visible   False Then x000D          Exit Sub x000D      End If x000D       x000D      Dim currentValue As String x000D      currentValue   Range Me TempCombo LinkedCell  Value x000D       x000D      If Trim currentValue  amp  vbNullString    vbNullString Then x000D          Me TempCombo ListFillRange        amp  DATA RANGE x000D      Else x000D          If Me TempCombo ListIndex   -1 Then x000D               Dim listCount As Integer x000D               listCount   write matching items currentValue  x000D               Me TempCombo ListFillRange        amp  HELP COLUMN  amp   1    amp  HELP COLUMN  amp  listCount x000D               Me TempCombo DropDown x000D          End If x000D   x000D      End If x000D  End Sub x000D   x000D   x000D  Private Function write matching items currentValue As String  As Integer x000D      Dim xWs As Worksheet x000D      Set xWs   Application ActiveSheet x000D   x000D      Dim cell As Range x000D      Dim c As Range x000D      Dim firstAddress As Variant x000D      Dim count As Integer x000D      count   0 x000D      xWs Range HELP COLUMN  amp       amp  HELP COLUMN  Delete x000D      With xWs Range DATA RANGE  x000D          Set c    Find currentValue  LookIn  xlValues  x000D          If Not c Is Nothing Then x000D              firstAddress   c Address x000D              Do x000D                Set cell   xWs Range HELP COLUMN  amp       amp   count   1   x000D                cell Value   c Value x000D                count   count   1 x000D                x000D                Set c    FindNext c  x000D                If c Is Nothing Then x000D                  GoTo DoneFinding x000D                End If x000D             Loop While c Address  lt  gt  firstAddress x000D          End If x000D  DoneFinding  x000D      End With x000D       x000D      write matching items   count x000D   x000D  End Function x000D   x000D  Private Sub TempCombo KeyDown    x000D                  ByVal KeyCode As MSForms ReturnInteger    x000D                  ByVal Shift As Integer  x000D   x000D      Select Case KeyCode x000D          Case 9    Tab key x000D              Application ActiveCell Offset 0  1  Activate x000D          Case 13   Pause key x000D              Application ActiveCell Offset 1  0  Activate x000D      End Select x000D  End Sub x000D   x000D   x000D    Notes    ComboBoxe s MatchEntry must be set to 2 - fmMatchEntryNone  Don t forget to set ComboBox name to TempCombo I am using listFillRange to set ComboBox options  The range must be continuous  so  matching items are stored in a help column   I have tried accomplishing the same with ComboBox addItem  but it turned out to be hard to repaint list box as user types

User · Answer

ExtendOffice com offers a VBA solution that worked for me in Excel 2016   Here s my description of the steps   I included additional details to make it easier   I also modified the VBA code slightly   If this doesn t work for you  retry the steps or check out the instructions on the ExtendOffice page    Add data validation to a cell  or range of cells    Allow   List   Source    the range with the values you want for the auto-complete   drop-down    Click OK   You should now have a drop-down but with a weak auto-complete feature    With a cell containing your newly added data validation  insert an ActiveX combo box  NOT a form control combo box    This is done from the Developer ribbon   If you don t have the Developer ribbon you will need to add it from the Excel options menu    From the Developer tab in the Controls section  click  Design Mode    Select the combo box you just inserted   Then in the same ribbon section click  Properties    In the Properties window  change the name of the combo box to  TempComboBox     Press ALT   F11 to go to the Visual Basic Editor   On the left-hand side  double click the worksheet with your data validation to open the code for that sheet   Copy and paste the following code onto the sheet   NOTE   I modified the code slightly so that it works even with Option Explicit enabled at the top of the sheet   Option Explicit  Private Sub Worksheet SelectionChange ByVal target As Range   Update by Extendoffice  2018 9 21   Update by Chris Brackett 2018-11-30  Dim xWs As Worksheet Set xWs   Application ActiveSheet  On Error Resume Next  Dim xCombox As OLEObject Set xCombox   xWs OLEObjects  TempCombo      Added this to auto select all text when activating the combox box  xCombox SetFocus  With xCombox      ListFillRange   vbNullString      LinkedCell   vbNullString      Visible   False End With   Dim xStr As String Dim xArr   If target Validation Type   xlValidateList Then       The target cell contains Data Validation       target Validation InCellDropdown   False         Cancel the  SelectionChange  event      Dim Cancel As Boolean     Cancel   True       xStr   target Validation Formula1     xStr   Right xStr  Len xStr  - 1       If xStr   vbNullString 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   vbNullString 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    Tab key             Application ActiveCell Offset 0  1  Activate         Case 13   Pause key             Application ActiveCell Offset 1  0  Activate     End Select End Sub  Make sure the the  Microsoft Forms 2 0 Object Library  is referenced   In the Visual Basic Editor  go to Tools   References  check the box next to that library  if not already checked  and click OK   To verify that it worked  go to Debug   Compile VBA Project  Finally  save your project and click in a cell with the data validation you added   You should see a combo box with a drop-down list of suggestions that updates with each letter you type

User · Answer

If you don t want to go down the VBA path  there is this trick from a previous question   Excel 2010  how to use autocomplete in validation list  It does add some annoying bulk to the top of your sheets  and potential maintenance  should you need more options  adding names of people from a staff list  new projects etc   but works all the same

User · Answer

This is a solution how to make autocomplete drop down list with VBA      Firstly you need to insert a combo box into the worksheet and change its properties  and then running the VBA code to enable the autocomplete    Get into the worksheet which contains the drop down list you want it to be autocompleted  Before inserting the Combo box  you need to enable the Developer tab in the ribbon    a   In Excel 2010 and 2013  click File   Options  And in the Options dialog box  click Customize Ribbon in the right pane  check the Developer box  then click the OK button   b   In Outlook 2007  click Office button   Excel Options  In the Excel Options dialog box  click Popular in the right bar  then check the Show Developer tabin the Ribbon box  and finally click the OK button    Then click Developer   Insert   Combo Box under ActiveX Controls  Draw the combo box in current opened worksheet and right click it  Select Properties in the right-clicking menu  Turn off the Design Mode with clicking Developer   Design Mode  Right click on the current opened worksheet tab and click View Code  Make sure that the current worksheet code editor is opened  and then copy and paste the below VBA code into it       Code borrowed from extendoffice com   Private Sub Worksheet SelectionChange ByVal Target As Range   Update by Extendoffice  2018 9 21     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    Click File   Close and Return to Microsoft Excel to close the Microsoft Visual Basic for Application window  Now  just click the cell with drop down list  you can see the drop-down list is displayed as a combo box  then type the first letter into the box  the corresponding word will be completed automatically    Note  This VBA code is not applied to merged cells   Source   How To Autocomplete When Typing In Excel Drop Down List

User · Answer

There s a messy workaround at http   www ozgrid com Excel autocomplete-validation htm that basically works like this    Enable  Autocomplete for Cell Values  on Tools - Options  gt  Edit  Recreate the list of valid items on the cell immediately above the one with the validation criteria  Hide the lines with the list of valid items

User · Answer

None of the above mentioned solution worked  The one that seemed to work only provide the functionality for just one cell  Recently I had to enter a lot of names and without suggestions  it was a huge pain  I was fortunate enough to have this excel autocomplete add-in to enable the autocompletion  The down side is that you need to enable macro  but you can always turn it off later

User · Answer

Why not use a Pivot   Sample Table Filter Pivot

[excel] Excel data validation with suggestions/autocomplete

Examples related to excel

Examples related to validation

Examples related to tags