vba Excel 2010：如何在验证列表中使用自动完成功能

Excel 2010: how to use autocomplete in validation list

提问by Pieter

I'm using a large validation list on which a couple of vlookup() functions depend. This list is getting larger and larger. Is there a way to type the first letters of the list item I'm looking for, instead of manually scrolling down the list searching for the item?

我正在使用一个大型验证列表，几个 vlookup() 函数依赖于该列表。这个名单越来越大。有没有办法输入我要查找的列表项的第一个字母，而不是手动向下滚动列表来搜索该项？

I've done some Googling but this suggests that this is indeed possible in earlier versions of Excel, but not in Excel 2010. Hope you guys can help.

我已经做了一些谷歌搜索，但这表明这在早期版本的 Excel 中确实是可能的，但在 Excel 2010 中却没有。希望你们能提供帮助。

采纳答案by JMax

Here is a very good way to handle this (found on ozgrid):

这是一个很好的处理方法（在ozgrid上找到）：

Let's say your list is on Sheet2and you wish to use the Validation List with AutoComplete on Sheet1.

假设您的列表已打开，Sheet2并且您希望使用带有自动完成功能的验证列表Sheet1。

On Sheet1A1Enter =Sheet2!A1and copy down including as many spare rows as needed (say 300 rows total). Hide these rows and use this formula in the Refers to: for a dynamic named range called MyList:

在Sheet1A1Enter=Sheet2!A1并向下复制，包括所需的尽可能多的备用行（例如总共 300 行）。隐藏这些行并在引用中使用此公式：对于名为 MyList 的动态命名范围：

=OFFSET(Sheet1!$A,0,0,MATCH("*",Sheet1!$A:$A0,-1),1)

Now in the cell immediately below the last hidden row use Data Validation and for the List Source use =MyList

现在在最后一个隐藏行下方的单元格中使用数据验证和列表源使用 =MyList

[EDIT]Adapted version for Excel 2007+ (couldn't test on 2010 though but AFAIK, there is nothing really specific to a version).
Let's say your data source is on Sheet2!A1:A300and let's assume your validation list (aka autocomplete) is on cell Sheet1!A1.

[编辑]Excel 2007+ 的改编版本（虽然无法在 2010 上进行测试，但 AFAIK，没有任何特定于某个版本的内容）。
假设您的数据源已打开，Sheet2!A1:A300并假设您的验证列表（又名自动完成）在 cell 上Sheet1!A1。

1. Create a dynamic named range MyListthat will depend on the value of the cell where you put the validation

   =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

2. Add the validation list on cell Sheet1!A1that will refert to the list =MyList

1. 创建一个动态命名范围MyList，该范围取决于您放置验证的单元格的值

   =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

2. 在Sheet1!A1将引用列表的单元格上添加验证列表=MyList

Caveats

注意事项

1. This is not a realautocomplete as you have to type first and then click on the validation arrow : the list will then beginat the first matching element of your list

2. The list will go till the end of your data. If you want to be more precise (keep in the list only the matchingelements), you can change the COUNTAwith a SUMLPRODUCTthat will calculate the number of matching elements

3. Your source list must be sorted

1. 这不是真正的自动完成，因为您必须先输入，然后单击验证箭头：然后列表将从列表的第一个匹配元素开始

2. 该列表将一直持续到您的数据结束。如果您想更精确（仅将匹配元素保留在列表中），您可以更改COUNTAwith aSUMLPRODUCT将计算匹配元素的数量

3. 您的来源列表必须排序

回答by gwapongkabayo

Building on the answer of JMax, use this formula for the dynamic named range to make the solution work for multiple rows:

基于JMax的答案，对动态命名范围使用此公式以使解决方案适用于多行：

=OFFSET(Sheet2!$A,MATCH(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN(),4))&"*",Sheet2!$A:$A0,0)-1,0,COUNTA(Sheet2!$A:$A))

回答by Anan Phungmit

=OFFSET(NameList!$A:$A0,MATCH(INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*",NameList!$A:$A0,0)-1,0,COUNTIF($A:$A0,INDIRECT("FillData!"&ADDRESS(ROW(),COLUMN(),4))&"*"),1)

1. Create sheet name as Namelist. In column A fill list of data.

2. Create another sheet name as FillDatafor making data validation list as you want.

3. Type first alphabet and select, drop down menu will appear depend on you type.

1. 创建工作表名称为Namelist. 在 A 列中填充数据列表。

2. 创建另一个工作表名称，FillData以便根据需要制作数据验证列表。

3. 输入第一个字母并选择，下拉菜单将根据您的输入出现。

回答by tomashm

Here's another option. It works by putting an ActiveX ComboBox on top of the cell with validation enabled, and then providing autocomplete in the ComboBox instead.

这是另一种选择。它的工作原理是将 ActiveX ComboBox 放在启用验证的单元格顶部，然后在 ComboBox 中提供自动完成功能。

Option Explicit

' Autocomplete - replacing validation lists with ActiveX ComboBox
'
' Usage:
'   1. Copy this code into a module named m_autocomplete
'   2. Go to Tools / References and make sure "Microsoft Forms 2.0 Object Library" is checked
'   3. Copy and paste the following code to the worksheet where you want autocomplete
'      ------------------------------------------------------------------------------------------------------
'      - autocomplete
'      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'          m_autocomplete.SelectionChangeHandler Target
'      End Sub
'      Private Sub AutoComplete_Combo_KeyDown(ByVal KeyCode As msforms.ReturnInteger, ByVal Shift As Integer)
'          m_autocomplete.KeyDownHandler KeyCode, Shift
'      End Sub
'      Private Sub AutoComplete_Combo_Click()
'          m_autocomplete.AutoComplete_Combo_Click
'      End Sub
'      ------------------------------------------------------------------------------------------------------

' When the combobox is clicked, it should dropdown (expand)
Public Sub AutoComplete_Combo_Click()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object
    If cbo.Visible Then cb.DropDown
End Sub

' Make it easier to navigate between cells
Public Sub KeyDownHandler(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Const UP As Integer = -1
    Const DOWN As Integer = 1

    Const K_TAB_______ As Integer = 9
    Const K_ENTER_____ As Integer = 13
    Const K_ARROW_UP__ As Integer = 38
    Const K_ARROW_DOWN As Integer = 40

    Dim direction As Integer: direction = 0

    If Shift = 0 And KeyCode = K_TAB_______ Then direction = DOWN
    If Shift = 0 And KeyCode = K_ENTER_____ Then direction = DOWN
    If Shift = 1 And KeyCode = K_TAB_______ Then direction = UP
    If Shift = 1 And KeyCode = K_ENTER_____ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_UP__ Then direction = UP
    If Shift = 1 And KeyCode = K_ARROW_DOWN Then direction = DOWN

    If direction <> 0 Then ActiveCell.Offset(direction, 0).Activate

    AutoComplete_Combo_Click
End Sub

Public Sub SelectionChangeHandler(ByVal Target As Range)
    On Error GoTo errHandler

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim cbo As OLEObject: Set cbo = GetComboBoxObject(ws)
    Dim cb As ComboBox: Set cb = cbo.Object

    ' Try to hide the ComboBox. This might be buggy...
    If cbo.Visible Then
        cbo.Left = 10
        cbo.Top = 10
        cbo.ListFillRange = ""
        cbo.LinkedCell = ""
        cbo.Visible = False
        Application.ScreenUpdating = True
        ActiveSheet.Calculate
        ActiveWindow.SmallScroll
        Application.WindowState = Application.WindowState
        DoEvents
    End If

    If Not HasValidationList(Target) Then GoTo ex

    Application.EnableEvents = False

    ' TODO: the code below is a little fragile
    Dim lfr As String
    lfr = Mid(Target.Validation.Formula1, 2)
    lfr = Replace(lfr, "INDIREKTE", "") ' norwegian
    lfr = Replace(lfr, "INDIRECT", "") ' english
    lfr = Replace(lfr, """", "")
    lfr = Application.Range(lfr).Address(External:=True)

    cbo.ListFillRange = lfr
    cbo.Visible = True
    cbo.Left = Target.Left
    cbo.Top = Target.Top
    cbo.Height = Target.Height + 5
    cbo.Width = Target.Width + 15
    cbo.LinkedCell = Target.Address(External:=True)
    cbo.Activate
    cb.SelStart = 0
    cb.SelLength = cb.TextLength
    cb.DropDown

    GoTo ex

errHandler:
    Debug.Print "Error"
    Debug.Print Err.Number
    Debug.Print Err.Description
ex:
    Application.EnableEvents = True
End Sub

' Does the cell have a validation list?
Function HasValidationList(Cell As Range) As Boolean
    HasValidationList = False
    On Error GoTo ex
    If Cell.Validation.Type = xlValidateList Then HasValidationList = True
ex:
End Function

' Retrieve or create the ComboBox
Function GetComboBoxObject(ws As Worksheet) As OLEObject
    Dim cbo As OLEObject
    On Error Resume Next
    Set cbo = ws.OLEObjects("AutoComplete_Combo")
    On Error GoTo 0
    If cbo Is Nothing Then
        'Dim EnableSelection As Integer: EnableSelection = ws.EnableSelection
        Dim ProtectContents As Boolean: ProtectContents = ws.ProtectContents

        Debug.Print "Lager AutoComplete_Combo"
        If ProtectContents Then ws.Unprotect
        Set cbo = ws.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
                            Left:=50, Top:=18.75, Width:=129, Height:=18.75)
        cbo.name = "AutoComplete_Combo"
        cbo.Object.MatchRequired = True
        cbo.Object.ListRows = 12
        If ProtectContents Then ws.Protect
    End If
    Set GetComboBoxObject = cbo
End Function

回答by Donna C

As other people suggested, you need to use a combobox. However, most tutorials show you how to set up just one combobox and the process is quite tedious.

正如其他人建议的那样，您需要使用组合框。但是，大多数教程只向您展示了如何设置一个组合框，而且这个过程非常乏味。

As I faced this problem before when entering a large amount of data from a list, I can suggest you use this autocomplete add-in. It helps you create the combobox on any cells you select and you can define a list to appear in the dropdown.

由于我之前在从列表中输入大量数据时遇到过这个问题，因此我建议您使用此自动完成加载项。它可以帮助您在您选择的任何单元格上创建组合框，您可以定义一个列表以显示在下拉列表中。

回答by Joel Spolsky

Excel automatically does this whenever you have a vertical column of items. If you select the blank cell below (or above) the column and start typing, it does autocomplete based on everything in the column.

只要您有垂直的项目列，Excel 就会自动执行此操作。如果您选择列下方（或上方）的空白单元格并开始键入，它会根据列中的所有内容自动完成。