再次在Excel数据验证列表中自动完成建议 [英] Autocomplete suggestion in Excel data validation list again

问题描述

键入时如何在Excel数据验证列表中提出建议.我的要求有限制:

How to make suggestions in Excel data validation list while typing. There are constraints in my request:

1. 项目列表应位于另一张表中，并且不得位于隐藏行的上方.
2. 键入一个短语应将列表缩小到包含该短语的所有项目.
3. 搜索应该不区分大小写.

因此，在键入am后，假设这些女孩的名字在项目列表中，我们应该建议从Amelia，Camila，Samantha中进行选择.

So after typing am we should hypothetically have a suggestion to pick up from Amelia, Camila, Samantha, provided that those girls' names are on the item list.

我在此处找到了很好的解决方案，但是它不会过滤带有contains子句的项目，而是过滤begins with.我很快在这里总结了提议的解决方案.

I have found a good solution here, however it does not filter the items with contains clause but begins with. I sum up the proposed solution here shortly.

1. 我们将组合框(ActiveX控件)插入到工作表中.

2. 我们右键单击工作表名称>查看代码>并将VBA代码粘贴到工作表VBA编辑器中:

1. We insert a Combo Box (ActiveX Control) to a sheet.

2. We right click on a sheet name > View code > and paste the VBA code in the sheet VBA editor:

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

我找不到修改方式来将搜索选项从开始于"更改为contains.

I could not find a way to modify to change the search option from 'begins with' to contains.

到目前为止，已提出有关验证列表中自动完成或自动建议的问题.
具有建议/自动完成功能的Excel数据验证
Excel 2010:如何在验证列表中使用自动填充
但是他们两个都没有一个答案能满足我的要求.

The questions about autocomplete or autosuggest in validation list have been asked so far.
Excel data validation with suggestions/autocomplete
Excel 2010: how to use autocomplete in validation list
But neither of them contained answers which would satisfied the constraints I imposed.

要下载的测试文件是此处.

Test file for download is here.

推荐答案

尝试添加以下事件(另外两个).每次输入内容时，代码都会刷新ComboBox列表.

Try to add the following event (additionally the the other 2). Every time you enter something the code refreshes the ComboBox list.

Private Sub TempCombo_Change()
    With Me.TempCombo
        If Not .Visible Then Exit Sub
        .Clear 'needs property MatchEntry set to 2 - fmMatchEntryNone
        .Visible = False 'to refresh the drop down
        .Visible = True
        .Activate
        Dim xStr As String, xArr As Variant
        xStr = TempCombo.TopLeftCell.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        xArr = Split(xStr, Application.International(xlListSeparator))
        Dim itm As Variant
        For Each itm In xArr
            If InStr(1, itm, .Value, vbTextCompare) > 0 Or .Value = "" Then
                .AddItem itm
            End If
        Next itm
        .DropDown
    End With
End Sub

这篇关于再次在Excel数据验证列表中自动完成建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！