跳到主要內容

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

對於包含很多項的數據驗證下拉列表，您需要在列表中上下滾動以找到您需要的項或將整個單詞正確地鍵入列錶框中。 有沒有辦法讓下拉列表在輸入相應字符時自動完成？ 這將幫助人們在單元格中帶有下拉列表的工作表中更高效地工作。 本教程提供了兩種方法來幫助您實現它。

使用 VBA 代碼使下拉列表自動完成
輕鬆讓下拉列表在 2 秒內自動完成
下拉列表的更多教程...

使用 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應用程序 窗口。

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

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

輕鬆讓下拉列表在 2 秒內自動完成

對於大多數Excel用戶來說，上述VBA方法很難掌握。 但是隨著 可搜索的下拉列表 的特點 Kutools for Excel，您可以輕鬆地為數據驗證下拉列表啟用自動完成功能 範圍中， 當前工作表中， 當前工作簿 or 一個特定的工作表 僅需 2 秒即可滿足您的需求。 此外，此功能適用於所有 Excel 版本。

尖端: 在應用此工具之前，請先安裝 Kutools for Excel 首先。 立即前往免費下載.

  1. 選擇 庫工具 > 下拉列表 > 可搜索的下拉列表.
  2. 在 可搜索的下拉列表 對話框中，指定要在其中應用此功能的位置 設定範圍 部分，然後單擊 OK 保存設置。
結果

配置完成後，單擊指定範圍內的下拉列表單元格將彈出一個列錶框。 當您輸入字符時，只要一個項目完全匹配，整個單詞就會自動完成。

備註: 要應用此功能，請 下載並安裝 Kutools for Excel 第一。

相關文章：

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

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

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

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

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

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

增強您的 Excel 技能 Kutools for Excel，體驗前所未有的效率。 Kutools for Excel 提供 300 多種高級功能，可提高工作效率並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905

Office Tab 為 Office 帶來選項卡式界面，讓您的工作更輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (325)
No ratings yet. Be the first to rate!
Cristiane
Cristiane
This comment was minimized by the moderator on the site
Hi, many thanks for this material. It helped a lot.
Reply
0
0
catherine
catherine
This comment was minimized by the moderator on the site
Hello,

following on from my last comment:

I'm setting up 'standard' rows with the dropdown lists in them. These I'm wanting to copy below into an extensive spreadsheet.
Unfortunately the dropdown's don't copy down when I do that after using the VBA.
Is there a way to do whats described above?

Cheers,
Catherine
Reply
Report
0
0
crystal
crystal     catherine
This comment was minimized by the moderator on the site
Hi Catherine,
The autocomplete drop-down lists generated by VBA code cannot be copied. You can only copy selected items that are displayed in the drop-down cell. Sorry for the inconvenience.
Reply
Report
0
0
catherine
catherine
This comment was minimized by the moderator on the site
Hello,

Have implemented this successfully, so thank you for this page and code.
But now I have an associated problem...:

After doing all of the above points, the code works as described, but I cannot do any undo/redo actions in the entire spreadsheet.
Is there a way to turn this combo box on for when I want to work with the dropdowns and off for when I want to work in other cells? so that the undo/redo actions are possible again?
and note, yes I have turned the design mode off after doing the above steps, but it still doesn't help the problem.

Cheers,
Catherine
Reply
Report
0
0
crystal
crystal     catherine
This comment was minimized by the moderator on the site
Hi Catherine Foley,
If you want to use the undo operation on cells other than the drop-down cells, the following VBA code can help you. Please give it a try.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2022/09/22
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    On Error Resume Next

    Set xWs = Application.ActiveSheet
    
    Set xCombox = xWs.OLEObjects("TempCombo")
    If Target.Validation.Type <> 3 Then
        If xCombox.Visible Then
            xCombox.Visible = False
        End If
        Exit Sub
    End If
    
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
'        .Visible = False
    End With

    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 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
Reply
Report
0
0
Yoni
Yoni
This comment was minimized by the moderator on the site
I hope now it will be upload
Reply
Report
0
0
Mbuchmeier
Mbuchmeier
This comment was minimized by the moderator on the site
This code works really well for a single drop down. However, if I have 5 dropdowns using the same list of values but they need to operate independently of each other how is that accomplished? Despite my best efforts, if I put more than one combo box on the page, any selection is one is mirrored in the other.
Reply
Report
0
0
crystal
crystal     Mbuchmeier
This comment was minimized by the moderator on the site
Hi Mbuchmeier,Only need one combo box on the page. The code works on all drop-down lists in current page. You just need to manually click on the drop-down list cell to activate the combo box.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi crystal,
I am working with the combo box (The VBA code from 2021/11/05). It is working great and very useful but there are 2 issues:1. Issue #1: Usually when you work on any cell in Excel, there is indication on the row number which indicate which row you work about. When the combo box is opened this indication is missing2.  Issue #2: When the combo box is opened in the last column of the dynamic table, then it isn't  working as usual: when I select value from the list, the cell remain empty until I move the cursor to another cell
Attached screenshot.
Thank in advance
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi Yoni,Thanks for your feedback. For the issues you mentioned:1. Issue #1: Excel does not highlight the row number when selecting an object in the worksheet. Since we use a combo box to replace the data validation drop-down list to handle the autocomplete operation, we can't show the indication in this case;2. Issue #2: I have tried as you described, but the problem cannot be reproduced. The screenshot you attached does not display, you need to save the screenshot and uplode it via the "Upload files" button below.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi Crystal,Thanks a lot for your response. Attached the screenshot. hope it will work now
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi Yoni,Sorry for the inconvenience. The screenshot you uploaded is still not shown on the page. You need to save the screenshot on your disk in advance and then uplode it via the "Upload files" button. See the attached screenshot.
View Attachments (1 / 5)
Report
0
0
B.K
B.K
This comment was minimized by the moderator on the site
will, thank you is not enugh :::: alot of thank you then ;;;;; that worked like magic :)
Reply
0
0
Nick M
Nick M
This comment was minimized by the moderator on the site
Yeah, basically completely useless.  Want the cell to auto-complete when typing in a cell using list data validation.Tried this, and now I have to start over from scratch because I can't Undo it.  Thanks.Also loaded with syntax errors.
Reply
0
0
Ahmed
Ahmed
This comment was minimized by the moderator on the site
It works as it should be except for two things, first, there is no validation after insertion. i.e. if I typed anything at the combo then clicked enter it will accept the typed value while it should not do this since the data validation is being used to prevent such behaviors and make sure that the entered data is from selected range. Second, at the data validation list, sometimes I use big range with empty cells and select ignore empty at validation list but the combo takes all the range and shows it, will be nice to remove the empty cells from the combo range.
Thanks & hope you can implement these things to make it perfect.
Reply
0
0
crystal
crystal     Ahmed
This comment was minimized by the moderator on the site
Hi, AhmedThe VBA below helps to solve the problem of blank cells. Since the Combo box accepts the value that are not in the list, we still can't find a way to solve this problem. Sorry for the inconvenience.
<div data-tag="code">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2021/11/05
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr, xArr1
Dim xRg As Range
Dim xSrc As Variant
Dim xI, xJ, xIndex, xCount As Integer

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 = Range(xStr)
If xRg Is Nothing Then
xArr1 = Split(xStr, ",")
Else
ReDim xArr1(0 To xRg.Count - 1)
For xI = 0 To xRg.Count - 1
xArr1(xI) = xRg.Item(xI + 1).Value
Next
End If
xI = 0
xCount = UBound(xArr1) + 1
For xJ = 0 To UBound(xArr1)
If Replace(xArr1(xJ), " ", "") = "" Then xCount = xCount - 1
Next
ReDim xArr(0 To xCount - 1)
xIndex = 0
For xJ = 0 To UBound(xArr1)
If Replace(xArr1(xJ), " ", "") <> "" Then
xArr(xIndex) = xArr1(xJ)
xIndex = xIndex + 1
End If
Next
xStr = ""
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
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Hi Crystal,
I am working with the combo box (The VBA code from 2021/11/05). It is working great and so useful! thanks a lot.
I found the source of my problem. There was a line of freeze panes which override the combo box.
The only problem now is that the Undo is not working. Any idea?
Thanks in advanced,
Yoni
Reply
Report
0
0
crystal
crystal     Yoni
This comment was minimized by the moderator on the site
Hi,
The code does not support Undo. Sorry for the inconvenience.
Reply
Report
0
0
Yoni
Yoni     crystal
This comment was minimized by the moderator on the site
Thanks. Maybe do you know why Worksheet_Change event is not firing once I choose value from the combo box?
Reply
Report
0
0
Syu
Syu     crystal
This comment was minimized by the moderator on the site
Hi, could you please doublecheck this code, when I use it instead of the dropdown list appearing, the source for the dropdown appears instead. This is the exact function I want, could you please fix it.
Reply
0
0
Sven
Sven     Syu
This comment was minimized by the moderator on the site
If you use a named range or something similar, like I have with a table column, it will display the named range name instead of the values in that named range. To get what you want, you need to change the xStr to display the worksheet range of that named range, since the .ListFillRange does not take in named ranges directly. This should accomplish it : dim RangeAddress as String: RangeAddress = Range(YourNamedRange).addressand then make the xStr something like this: xStr = "YourWorksheetNameWithTheNamedRangeInIt!" & RangeAddress (important: add the '!' for the sheet reference)
This will make your xStr look something like: NamedRangeSheet!$A$1:$A$5
xStr = Right(xStr, Len(xStr) - 1)

If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.TempCombo.List = xArr
End If

with these adjustments your combobox should display the list values instead of the source
Reply
0
0
crystal
crystal     Syu
This comment was minimized by the moderator on the site
Hi Syu,Sorry I don't quite understand your description. Can you try to be more specific of it?After applying the VBA code mentioned in the above method, when the cell with the data validation drop-down list is checked, the drop-down list turn into a combo box, and then all the items in the list are listed. 
Reply
Report
0
0
anton
anton     crystal
This comment was minimized by the moderator on the site
do you have an example file please?
Reply
0
0
xxxZIDANExxx
xxxZIDANExxx
This comment was minimized by the moderator on the site
Hello. The code is very cool. Please make sure that the formula "INDIRECT" is carried out and displayed. It is very necessary
Reply
0
0
Zaher
Zaher
This comment was minimized by the moderator on the site
The code is not working for a validation list which is created by vba code and the source is a named range
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations