excel - 如何防止 Excel 组合框显示重复的条目和/或空格？

问题描述

我创建了一个 Excel 工作表（名为“用户”），它在 A 列中有一个随机名称列表。此列中的每个单元格都链接到一个数据验证单元格。当用户在此数据验证单元格中键入一个字符并选择下拉向下箭头，此字符（或单词）作为参数传递给每个名称的 Excel 搜索函数。如果搜索结果返回 1，则该名称将合并为动态列表（列 D）的一部分，其中轮流显示在数据验证单元格中。这可以在下面看到，我输入了字符“A”并返回了一个以该字符开头的名称列表。

我进一步创建了一个单独的工作表（名为“master”），其中列 A 现在在每个单元格中都包含一个数据验证列表。我使用了 VBA 代码，因此当用户双击这些数据验证单元格之一时，它的引用作为参数传递给先前在工作表“用户”中使用的搜索函数。请看下面：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim SortCell As Variant

    Dim master As Worksheet
    Dim users As Worksheet


    Set master = ThisWorkbook.Sheets("master")
    Set users = ThisWorkbook.Sheets("users")
    Set cboTemp = master.OLEObjects("DataCombo")

    On Error Resume Next

On Error GoTo errHandler

   'If found Data Validation cell
    If Target.Validation.Type = 3 Then
       'MsgBox Target.Address
        SortCell = "master!" & Target.Address

        'Set Target Address to that of search function in InCell column
        With users
            .Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
            .Range("B2:B131").FillDown
        End With 
    End If

我再次取得了进展，在这种情况下，当用户双击工作表“master”的 A 列中的这些数据验证列表之一时，会出现一个组合框，其中包含来自“用户”的所有随机名称的下拉列表床单。当用户在组合框中键入时，名称列表被过滤以反映在“用户”列 D 中创建的动态列表。完整的 VBA 代码如下所示：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim SortCell As Variant

    Dim master As Worksheet
    Dim users As Worksheet


    Set master = ThisWorkbook.Sheets("master")
    Set users = ThisWorkbook.Sheets("users")
    Set cboTemp = master.OLEObjects("DataCombo")

    On Error Resume Next

On Error GoTo errHandler

    'If found Data Validation cell
    If Target.Validation.Type = 3 Then

        'if the cell contains a data validation list
        Cancel = True
        Application.EnableEvents = False

        'MsgBox Target.Address
        SortCell = "master!" & Target.Address

        'Set Target Address to that of search function in InCell column
        With users
            .Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
            .Range("B2:B131").FillDown
        End With

        'Get Data Validation Formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)

        'MsgBox str

        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5 
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With

        cboTemp.Activate
        Me.DataCombo.DropDown

    End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

我遇到的问题是，当用户键入组合框并因此根据键入的字符创建新的名称列表时，组合框保留动态列表的原始长度 - 它不会减小大小以反映从搜索中返回的名称数量减少。此外，留下空白并重复名称以“弥补”这个剩余长度。请看下图，它说明了这一点：

我为我的问题的长度和细节道歉，但我想知道我如何能够防止组合框以这种方式运行并使其能够动态减小其下拉列表大小以匹配列表的长度的名字返回？

欢迎任何想法/建议。

标签： excelvba

Private Sub DataCombo_Change() 'When user types into the combo box, this will resize the dropdown list to match the length of the dynamic list created in "users" With Me.DataCombo .Visible = True .ListFillRange = "Employees" End With End Sub

excel - 如何防止 Excel 组合框显示重复的条目和/或空格？

问题描述

解决方案

推荐阅读