☰

Excel“智能”下拉菜单/数据验证

tag-icon • tag-icon microsoft-excel worksheet-function vba data-validation
Excel“智能”下拉菜单/数据验证

在 Excel 中，是否有办法创建下拉菜单或数据验证字段，

  • 允许用户输入当前不在列表中的值（如组合框）
  • 将该值保存在下拉列表中，以便下次用户在新字段中拉出该列表时，他们输入的新值就会出现在下拉列表中
  • 从下拉列表中删除重复的条目（即，如果用户在列中多次输入值“Apple”，则它只会在下拉列表中出现一次）
  • （可选）按字母顺序对下拉菜单进行排序

我尝试的第一件事是将数据验证应用于列，将其设置为列表，并将列表源设置为同一列。这设法实现了前两个要点，但不幸的是，这样做每次在列中使用时都会重复每个值（并且它也不能很好地对列表进行排序）。

任何帮助都将不胜感激！

答案1

下面的代码生成一个组合列表（数据验证）和：

  • 允许用户输入当前不在列表中的值
  • 将所有新值添加到下拉列表
  • 删除重复条目
  • 修剪列表中的所有值
  • 按字母顺序对列表进行排序

代码粘贴位置：

  • 打开 VBA 编辑器：Alt+F11

    1. 第一个子Worksheet_Change()必须插入工作表的 VBA 模块

  • 所有其他子程序和函数（第 1 和第 2 部分）都应粘贴到新的 VBA 模块中

    1. 在编辑器中点击菜单插入>模块并粘贴新代码

。

在Sheet1 模块（Microsoft Excel 对象，VBA 编辑器左上角）：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Columns.Count = 1 Then setList Target
End Sub

。

1 之 2（新的 VBA 模块）：

Option Explicit

Public Sub setList(ByRef rng As Range, Optional fullColumn As Boolean = True)
   Dim ws As Worksheet, lst As Range, lr As Long

   If rng.Columns.Count = 1 Then
      xlEnabled False
      Set ws = rng.Parent
      Set lst = ws.UsedRange.Columns(rng.Column)
      lr = setLastRow(lst, rng.Column)
      If lr > 1 Then
         If fullColumn Then Set lst = ws.Columns(rng.Column)
         With lst.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=getDistinct(lst, lr)
            .ShowError = False
         End With
      End If
      xlEnabled True
   End If
End Sub

Private Function setLastRow(ByRef rng As Range, ByVal lc As Long) As Long
   Dim ws As Worksheet, lr As Long
   If Not rng Is Nothing Then
      Set ws = rng.Parent
      lr = ws.Cells(rng.Row + ws.UsedRange.Rows.Count + 1, lc).End(xlUp).Row
      Set rng = ws.Range(ws.Cells(1, lc), ws.Cells(lr, lc)) 'updates rng (ByRef)
   End If
   setLastRow = lr
End Function

Public Sub xlEnabled(ByVal onOff As Boolean)
    Application.ScreenUpdating = onOff
    Application.EnableEvents = onOff
End Sub

2 之 2：

Private Function getDistinct(ByRef rng As Range, ByVal lr As Long) As String
   Dim ws As Worksheet, lst As String, lc As Long, tmp As Range

   Set ws = rng.Parent
   lc = ws.Cells(rng.Row, rng.Column + ws.UsedRange.Columns.Count + 1).End(xlToLeft).Column
   Set tmp = ws.Range(ws.Cells(1, lc + 1), ws.Cells(lr, lc + 1))

   If tmp.Count > 1 Then
      With tmp.Cells(1, 1)
         .Formula = "=Trim(" & ws.Cells(rng.Row, lc).Address(False, False) & ")"
         .AutoFill Destination:=tmp
      End With

      tmp.Value2 = tmp.Value2
      tmp.RemoveDuplicates Columns:=1, Header:=xlNo
      lr = setLastRow(tmp, lc + 1)

      ws.Sort.SortFields.Add Key:=ws.Cells(lr + 1, lc + 1), Order:=xlAscending
      With ws.Sort
         .SetRange tmp
         .Header = xlNo
         .MatchCase = False
         .Orientation = xlTopToBottom
         .Apply
      End With

      setLastRow tmp, lc + 1
      lst = Join(Application.Transpose(tmp), ",")
      tmp.Cells(1, 1).EntireColumn.Delete
   End If

   getDistinct = lst

End Function

每当您输入新值时（在任何列中）

  • 代码暂时关闭 ScreenUpdating 和事件
  • 当前列的所有先前数据验证都将被删除
  • 它确定工作表上最后使用的列，以及当前列中包含数据的最后一个单元格

  • 检查下拉菜单是否应应用于整列或仅应用于包含数据的单元格

    • 可以通过更改fullColumn As Boolean = True为来切换此选项False

  • 函数 getDistinct()：

    • 将当前列中的所有值复制到工作表上第一个未使用的列
    • 这不是正常的复制\粘贴操作
    • 它将 TRIM() 应用于新列中当前列的所有单元格
    • 然后将公式结果转换为字符串
    • 仅适用RemoveDuplicates于此新系列
    • 它对剩余列表进行排序
    • 再次确定列表大小，并将范围转换为以逗号分隔的项目字符串

  • Sub setList() 将列表应用于生成下拉列表的新验证规则

    • 可以从数据 > 数据验证（选择列并全部清除）中删除此验证规则

  • 可以通过注释掉一行来关闭它：

。

Private Sub Worksheet_Change(ByVal Target As Range)
   'If Target.Columns.Count = 1 Then setList Target
End Sub

相关内容