Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

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

如果您有一個包含大項目的數據驗證下拉列表,則需要在列表中上下滾動以查找合適的列表,或者直接在列錶框中鍵入整個單詞。 如果在下拉列表中鍵入第一個字母時有允許自動完成的方法,一切將變得更加容易。 本教程將提供一種VBA方法來幫助您實現它。

使用VBA代碼輸入下拉列表時自動完成
下拉列表的更多教程...


使用VBA代碼輸入下拉列表時自動完成

請執行以下操作,以在單元格中鍵入相應字母后使下拉列表自動完成。

首先,您需要在工作表中插入一個組合框並更改其屬性。

1.打開包含要使其自動完成的下拉列表單元格的工作表。

2.在插入組合框之前,需要將“開發人員”選項卡添加到Excel功能區。 如果功能區上顯示“開發人員”標籤, 轉到步驟3。 否則,請執行以下操作:單擊 文件 > 選項 打開 選項 窗口。 在這個 Excel選項 窗口中,單擊 自定義功能區 在左窗格中,檢查 開發人員 框,然後單擊 OK 按鈕。 看截圖:

3。 點擊 開發人員 > 插入 > 組合框(ActiveX控件).

4.在當前工作表中繪製一個組合框。 右鍵單擊它,然後選擇 精選樓盤 從右鍵單擊菜單中。

5。 在裡面 精選樓盤 對話框中,請替換原始文本 (姓名) 場與 TempCombo。

6。 關閉 設計模式 通過點擊 開發人員 > 設計模式。

然後,應用下面的VBA代碼

7.右鍵單擊當前工作表選項卡,然後單擊 查看代碼 從上下文菜單中。 看截圖:

8.在開幕 Microsoft Visual Basic for Applications 窗口,請將以下VBA代碼複製並粘貼到工作表的“代碼”窗口中。

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

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。 按 其他 + Q 同時關閉按鍵 Microsoft Visual Basic應用程序 窗口。

從現在開始,單擊下拉列表單元格時,下拉列表將自動提示。 您可以開始輸入字母,以使所選單元格中的相應項目自動完成。 看截圖:

備註: 此代碼不適用於合併的單元格。

使用Excel中的複選框輕鬆創建下拉列表:

帶有復選框的下拉列表 的效用 Kutools for Excel 可以幫助您根據需要在指定範圍,當前工作表,當前工作簿或所有打開的工作簿中輕鬆創建帶有復選框的下拉列表。
立即下載並嘗試! (30天免費試用)


相關文章:

如何在Excel中使用多個複選框創建下拉列表?
許多Excel用戶傾向於創建帶有多個複選框的下拉列表,以便每次都從列表中選擇多個項目。 實際上,您無法使用數據驗證功能創建帶有多個複選框的列表。 在本教程中,我們將向您展示兩種在Excel中創建帶有多個複選框的下拉列表的方法。 本教程提供了解決問題的方法。

在Excel中從另一個工作簿創建下拉列表
在工作簿中的工作表之間創建數據驗證下拉列表非常容易。 但是,如果數據驗證所需的列表數據位於另一個工作簿中,您將怎麼辦? 在本教程中,您將詳細了解如何從Excel中的另一個工作簿創建拖放列表。

在Excel中創建可搜索的下拉列表
對於具有眾多價值的下拉列表,找到合適的價值並非易事。 以前,我們已經介紹了一種在下拉框中輸入第一個字母時自動完成下拉列表的方法。 除了自動完成功能之外,您還可以使下拉列表可搜索,以提高在下拉列表中查找適當值時的工作效率。 為了使下拉列表可搜索,請嘗試本教程中的方法。

在Excel下拉列表中選擇值時自動填充其他單元格
假設您已經根據單元格區域B8:B14中的值創建了一個下拉列表。 在下拉列表中選擇任何值時,都希望在選定單元格中自動填充單元格範圍C8:C14中的相應值。 為了解決該問題,本教程中的方法將對您有所幫助。

下拉列表的更多教程...


最佳辦公效率工具

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

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

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
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.
    stefany · 14 days ago
    hi, thanks for your code. I would like to autocomplete and make multiple selections in a drop down list. Can you help me please?, thank you
  • To post as a guest, your comment is unpublished.
    krish · 3 months ago
    Hello thanks for the code it works great.
    But i want to auto expand drop down list when i move to next cell by pressing enter key without clicking on arrow button (or) alt+down.
    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    taras.samolyuk · 3 months ago
    Hello, is there a chance to add dependency to select date and in the next column to the proposed choices?
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Good day,
      Would you provide a screenshot of what you are trying to do?
  • To post as a guest, your comment is unpublished.
    Christiam Alfonzo · 4 months ago
    como hago para agregar esta función
    =INDIRECTO("tabla1[columna]")
    al código
  • To post as a guest, your comment is unpublished.
    Danielle · 4 months ago
    I tried to use this method. I added the code. After that I add the range needed. Looks all fine. Then I test and the combobox dissapears directly after filling it. So the code works, but its not usable. What do I do wrong?
    Kind regards, Danielle
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      The code works well in my case. It is not easy to figure out the problem as you described above. Would you mind sending your worksheet to my email address?
  • To post as a guest, your comment is unpublished.
    coltan123 · 6 months ago
    Code is working , but data validation is not working. For example, when i set the data validation to be red/bue/yellow, it does not show error message when i type green
  • To post as a guest, your comment is unpublished.
    Mhyk · 7 months ago
    Hi I have some difficulties with my excel work. I would like to know if there is a method to autofill the cell I desire?
    Example
    1 = san pedro
    2 = santa rosa
    3 = calamba
    4 = san pablo
    and so on.

    I have a list with correspoding numbers. If I input number 1 in Cell A5 I would like it to automatically fill the Cell B5 with its corresponding list name (san pedro) and if I input 3 in A5, B5 will be = calamba.
    thank you. for the help, Im having trouble to find a solution to this hope to find answers.
    Thank you and God Bless.


    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi,
      Methods in this tutorial can do you a favor:
      How To Auto Populate Other Cells When Selecting Values In Excel Drop Down List?
      https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html
  • To post as a guest, your comment is unpublished.
    Sisi · 7 months ago
    Hi Guys,


    I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?
  • To post as a guest, your comment is unpublished.
    Simona Muresan · 7 months ago
    Hi Guys,


    I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?

  • To post as a guest, your comment is unpublished.
    Juan Carlos · 8 months ago
    Hello, I have a problem with this method and I already use a list validated with an indirect function to refer to the range of a table and when applying this macro it shows me only the indirect formula in the list! How could I solve this problem?
  • To post as a guest, your comment is unpublished.
    Leonardo · 8 months ago
    Hi! Is it possible to disable the combo box scrolling list when I click on the cell? and leave it active only if I click on the arrow, thanks!
  • To post as a guest, your comment is unpublished.
    Leonardo · 8 months ago
    Hello, is it possible to replicate this in a complete column? I mean that each individual row of a column has this possibility or at least 1000 rows in a simple way. Thank you!
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Leonardo,
      The VBA works for all data validation drop down lists in the worksheet.
  • To post as a guest, your comment is unpublished.
    gurita_22 · 8 months ago
    If you use this method with one combobox/sheet i guess it's flawless.

    I used this method with three comboboxes on one sheet with 3 different ListFillRange.
    Dropdown keep showing up on different combobox than what i'm typing in.
    1. I type and select correctly from the first.
    2. When i go to second combo, the third dropdown pops up.(I have to select the"", as it is still empty)
    then i can search my second combobox.
    3. When i go to the third, the first dropdown pops up, I reselect the only item(as there is only one that was already selected in the first step)
    After this i can add the content in the third dropdown.



    All subs are on sheet1's vba window.
    Now i'm trying to spit code in different modules, but can't really make it work.
    Search works, but i always have to push the dropdown arrow to see available results to select. Dropdown doesn't pop up at all.

    using 365, but i have to make "sheet" work on excel 2010
  • To post as a guest, your comment is unpublished.
    Rohit · 8 months ago
    The command works when first applied. But after saving the file, when i reopen it the drop down itself disappears.
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi Rohit,
      After applying the code, please save the workbook as an Excel Macro-Enabled Workbook before closing the file.
      • To post as a guest, your comment is unpublished.
        wendy · 2 months ago
        I have done that - saved as a macro-enabled workbook but it still all disappears when you close the file. Thanks, Wendy

  • To post as a guest, your comment is unpublished.
    ahmadzaid · 8 months ago
    I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’


    for info .. I use excel 2010

    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      Hi,
      I tried the VBA in Excel 2010 and it works well.
      The drop down list should be data validation drop-down list which is created by clicking Data > Data Validation....
      • To post as a guest, your comment is unpublished.
        ahmad zaid · 8 months ago
        It's work in cell with list but when i move or tap or select any cell with no list ..the up message talked you about always show..

  • To post as a guest, your comment is unpublished.
    Carlos · 9 months ago
    Hi! Thanks for this code!

    I'm getting a compile error saying "variable not defined", it highlights the "cancel = true" line
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi Carlos,
      Which Excel version are you using?
      • To post as a guest, your comment is unpublished.
        ahmadzaid · 8 months ago
        I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’


        fo info. I use excel 2010
      • To post as a guest, your comment is unpublished.
        Carlos · 9 months ago
        Hi. Office 365 Pro Plus
        • To post as a guest, your comment is unpublished.
          crystal · 9 months ago
          Hi Carlos,
          Remove the "cancel = true" line from the VBA, then try again.
  • To post as a guest, your comment is unpublished.
    Mitchell · 9 months ago
    Hi Crystal,

    Absolutely perfect and thank you for the code. However when i press enter the cell automatically goes down. How can I make the cell go to the right when I press enter?

    MItchell

  • To post as a guest, your comment is unpublished.
    Sergio · 10 months ago
    Hi,

    Is it possible to show the matches with the caracter your typping using this code and format of combox?

    Example:

    You have a list with Onion, Black Onion, Green Onion, Red Onion...

    And in the combox you type: "On", so the drop down list show all the values existing on the list that matchs with "On".

    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi Sergio,
      The method in this article can help you:
      How To Create A Searchable Drop Down List In Excel?
      https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html
  • To post as a guest, your comment is unpublished.
    Reinaldo Yajure · 10 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 · 9 months ago
      Hi Reinaldo Yajure,
      Please apply the below VBA code. The problem is fixed. Sorry for the inconvenience.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 2020/05/19
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      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 xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      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.
        Alistair · 7 months ago
        Hi there, this is a great solution, but for some reason, in the original code, the combox was hidden when the cell with the data validation was not active, and only visible when the cell was selected.
        With this code, we get undo back, which is perfect, but the combox does not auto-hide again when you click off.
        Do you have a solution for this?

        Thanks,

        Alistair
  • To post as a guest, your comment is unpublished.
    Sandeep Sharma · 11 months ago
    is there any way to increase the list size as it is showing up only 10 line items as i have 12 lines
    • To post as a guest, your comment is unpublished.
      crystal · 9 months ago
      Hi Sandeep Sharma,
      In the Properties dialog box of the Combo box (step 5 of this tutorial), please find the ListRows field and then change the number 8 to 12 under the Alphabetic tab.
  • To post as a guest, your comment is unpublished.
    Jonas · 11 months 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 · 11 months 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 · 11 months 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 years 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 years 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 years ago
        Looks good.Thanks Crystal.
  • To post as a guest, your comment is unpublished.
    Billy · 1 years 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 years ago
      Hi Billy,
      Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Angga S.P. · 1 years ago
    Woooooooowwwwww very useful, thankyou for sharing, god bless you
  • To post as a guest, your comment is unpublished.
    Big.Moe · 1 years 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 years 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 years 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 · 1 years 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. · 1 years 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 years 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 · 1 years ago
    It works. Thank you and God bless
  • To post as a guest, your comment is unpublished.
    Kuku · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years ago
    Hi! Does Anyone Know, how use this function just on a cell?
    • To post as a guest, your comment is unpublished.
      Thomas · 1 years 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 · 1 years 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 · 1 years 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. · 1 years ago
    Great tutorial. Thank you.
  • To post as a guest, your comment is unpublished.
    me · 1 years ago
    Doesn't work. Please update.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for the inconvenience. Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Nadine · 1 years 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 · 1 years 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 · 1 years 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 years 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 years 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.
        Donald · 7 months ago
        Hi Cristal, great answer. Now if on the same worksheet I want to add another combo box that calls data from a third sheet, how would the code look like as I tried and got no result. I will appreciate your support. Donald
      • To post as a guest, your comment is unpublished.
        Jonas · 11 months 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 · 1 years 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 · 1 years 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 years 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 · 1 years 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 · 1 years 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.
        Dilshan · 20 days ago
        Hi,
        I am trying to pull that list data from another sheet but it does not work. below is my code
        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 = "MAINInput!$D:$D"
        'MAINInput = sheet name where the list is available


        Set xWs = Application.ActiveSheet
        On Error Resume Next
        Set xCombox = xWs.OLEObjects("cmbEmployee")
        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.cmbEmployee.List = xArr
        End If
        .LinkedCell = Target.Address
        End With

        xCombox.Activate
        Me.cmbEmployee.DropDown
        End If
        End Sub
        Private Sub cmbEmployee_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 years 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 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years 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 · 1 years ago
    How can I set it to accept only the text in the list?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi RK,
      Sorry can't help you with that. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Anthony · 1 years 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 · 1 years 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 · 1 years ago
        No problem. I figured it out. Thanks, Crystal.
        • To post as a guest, your comment is unpublished.
          noob · 1 years ago
          Yeah want to know too
        • To post as a guest, your comment is unpublished.
          eeframe27 · 1 years 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 · 1 years 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 · 1 years 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 · 2 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 · 1 years 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 · 2 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.