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

Question

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

vbaexcelexcel-2010

23

我正在使用一个大型的验证列表，其中一些vlookup()函数依赖于它。这个列表越来越大。有没有一种方法可以输入我要查找的列表项的首字母，而不是手动滚动列表搜索该项?

我已经做了一些谷歌搜索，但这表明在Excel早期版本中确实可能实现这一点，但在Excel 2010中不行。希望你们能帮忙。

- Pieter

请查看这个维基，它是真正意义上最接近的解决方案。一个叫做“不要打破链条”或者在Excel中称为数据链接的概念。 - RBT

6个回答

2

在JMax的答案基础上，使用以下公式来创建动态命名范围，以使解决方案适用于多行：

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

- gwapongkabayo

2

这里有另一个选项。它通过在启用验证的单元格上方放置一个ActiveX组合框，然后在组合框中提供自动完成来实现。

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

- tomashm

0

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

创建名为 Namelist 的工作表。在 A 列中填写数据列表。
创建另一个名为 FillData 的工作表，以便根据需要创建数据验证列表。
输入第一个字母并选择，下拉菜单将根据您的输入出现。

- Anan Phungmit

数据列表必须按字母顺序排序。 - Anan Phungmit

我不明白。你说的“sheet name”是指工作表的名称还是已命名区域？对于你提供的代码应该怎么做？ - Przemyslaw Remin

0

当你有一个垂直列的项目时，Excel会自动完成这个操作。如果你选择该列下方（或上方）的空白单元格并开始输入，它会根据该列中的所有内容自动完成。

- Joel Spolsky

1

该列表存储在另一个工作表中。我使用数据验证-->列表。 - Pieter

0

正如其他人建议的那样，您需要使用组合框。然而，大多数教程只会向您展示如何设置一个组合框，这个过程相当繁琐。

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

- Donna C

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JMax · Accepted Answer

以下是一个很好的处理方法（来源于ozgrid）：

假设你的列表在Sheet2上，而你希望在Sheet1上使用带有自动完成的验证列表。

在Sheet1的A1单元格中输入=Sheet2!A1，并向下复制，包括所需的空行（例如共计300行）。隐藏这些行，并在“引用范围”中使用此公式来创建名为MyList的动态命名范围：

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

在最后一个隐藏行的下面单元格中使用数据验证，对于列表源，请使用=MyList

[编辑] 适用于Excel 2007+的修改版本（虽然无法在2010上进行测试，但据我所知，没有什么特定于版本的内容）。
假设您的数据源位于Sheet2!A1:A300，并且假设您的验证列表（也称为自动完成）位于单元格Sheet1!A1。

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

=OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))
在单元格Sheet1!A1上添加验证列表，引用列表=MyList

注意事项

这不是真正的自动完成，因为您必须先键入，然后再点击验证箭头：列表将从匹配列表的第一个元素开始
列表将继续到您的数据结尾。如果您想更加精确（仅保留匹配的元素），可以将COUNTA更改为SUMLPRODUCT，它将计算匹配元素的数量
您的源列表必须经过排序