提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel下拉列表中輸入時自動完成?

如果你有大值的數據驗證下拉列表中,您需要在列表中向下滾動只是為了找到合適的一個,或直接鍵入整個單詞到列表框中。 如果在下拉列表中輸入第一個字母時允許自動完成,所有內容都將變得更加簡單。 在本教程中,我們將向您提供在工作表中插入組合框並運行VBA代碼,以實現下拉列表中的自動完成功能。

使用VBA代碼在下拉列表中鍵入時自動完成


將多個工作表/工作簿合併到一個工作表/工作簿中:

將多個工作表或工作簿合併到一個工作表或工作簿中可能是您​​日常工作中的一項重大任務。 但是,如果你有 Kutools for Excel,其強大的實用性 - 結合 可以幫助您將多個工作表,工作簿快速合併到一個工作表或工作簿中。 立即下載Kutools for Excel的全功能60天免費試用版!

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 立即下載免費試用版!

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文本; 將數字和貨幣轉換為英語單詞...
  • 合併工具:多個工作簿和表格合二為一; 合併多個單元格/行/列而不丟失數據; 合併重複行和總和...
  • 拆分工具:根據價值將數據拆分為多個表格; 一個工作簿到多個Excel,PDF或CSV文件; 一列到多列......
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 創建郵件列表和 通過Cell的價值發送電子郵件...
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

使用VBA代碼在下拉列表中鍵入時自動完成

首先,您需要在工作表中插入組合框並更改其屬性,然後運行VBA代碼以啟用自動完成功能。

1。 進入包含您希望自動完成的下拉列表的工作表。

2。 在插入組合框之前,您需要啟用該組合框 開發人員 功能區中的標籤。

1)。 在Excel 2010和2013中,單擊 文件 > 選項。 而在中 選項 對話框,單擊 自定義功能區 在右窗格中,檢查 開發人員 框,然後單擊 OK 按鈕。 看截圖:

2)。 在Outlook 2007中,單擊 Office 按鈕> Excel選項。 在 Excel選項 對話框,單擊 流行 在右欄中,然後檢查 顯示開發者標籤在絲帶 框,最後點擊 OK 按鈕。

3。 然後點擊 開發人員 > 插入 > 組合框ActiveX控件。 看截圖:

4。 在當前打開的工作表中繪製組合框,然後右鍵單擊它。 選擇 屬性 在右鍵菜單中。

5。 在裡面 屬性 對話框中,您需要:

1)。 將名稱更改為 TempCombo名稱 領域;

2)。 指定您需要的字體 字體 領域;

3)。 向下滾動以選擇 1-fmMatchEntryCompleteMatchEntry 領域;

4)。 關上 屬性 對話框。

6。 單擊關閉設計模式 開發人員 > 設計模式.

7。 右鍵單擊當前打開的工作表選項卡並單擊 查看代碼。 看截圖:

8。 確保打開當前工作表代碼編輯器,然後復制並粘貼下面的VBA代碼。 看截圖:

VBA代碼:在下拉列表中輸入時自動完成

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

9。 點擊 文件 > 關閉並返回到Microsoft Excel 關閉 Microsoft Visual Basic for Application 窗口。

10。 現在,只需點擊帶有下拉列表的單元格,你可以看到下拉列表中顯示為一個組合框,然後輸入第一個字母入禁區,對應的字將被自動完成。 看截圖:

注意:此VBA代碼不適用於合併的單元格。

Office Tab - 在Excel中選項卡式瀏覽,編輯和管理工作簿:

Office選項卡將Web瀏覽器(如Google Chrome,Internet Explorer新版本和Firefox)中顯示的選項卡界面帶到Microsoft Excel。 它 是一個省時的工具,在你的工作中無可替代。 見下面的演示:

點擊免費試用Office Tab!

Excel的Office選項卡

相關文章:


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
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.
    me · 5 days ago
    Doesn't work. Please update.
  • To post as a guest, your comment is unpublished.
    Nadine · 13 days 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.
    Kenneth Augustine Ng · 1 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 · 1 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 · 1 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.
    Dominic De Franco · 2 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 · 1 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 · 2 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 · 2 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 · 1 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 · 1 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 2 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 · 4 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 · 3 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 · 5 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 · 4 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.
    Parth · 6 months 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 · 3 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 · 6 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 · 7 months 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 · 6 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 · 7 months 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 · 7 months 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 · 5 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 · 7 months 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 · 7 months 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 · 8 months 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 · 7 months 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 · 8 months 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 · 7 months ago
      Good day,
      Sorry can't fix the problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Victor Betancurt · 8 months 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 · 8 months 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 · 8 months 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 · 7 months 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 · 9 months 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 · 9 months 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 · 8 months 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 · 9 months 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.
    K. K. · 9 months 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.
      K. K. · 9 months 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 · 8 months 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 · 9 months 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 · 10 months 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.
    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.
    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 · 8 months ago
      Hi,
      Thank you for your comment.
      Sorry can't help you with that.
  • To post as a guest, your comment is unpublished.
    Pine Corn · 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 · 11 months 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.
    shade131ful · 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 John Fernandes · 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.
    Christopher Rivers · 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 · 1 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 · 1 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.
    Yvan · 1 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 · 1 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.
    Jordi · 1 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 · 1 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.
    Dan · 1 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 · 1 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.
    imre · 1 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?