Excel 实现下拉多选功能

1. 开启宏
2. 创建 sheet 级别的 vb 脚本
3. 将下面代码写入

Private Sub Worksheet_Change(ByVal Target As Range)Dim watchRange As RangeDim newVal As String, oldVal As StringDim items() As StringDim i As Long, result As String' 多选所在列：根据模板实际列号调整Set watchRange = Intersect(Target, Me.Range("D:D"))If watchRange Is Nothing Or Target.CountLarge > 1 Then Exit SubOn Error GoTo ExitHandlerApplication.EnableEvents = FalsenewVal = Target.Value          ' 用户当前输入/选择Application.UndooldVal = Target.Value          ' 原来的值（逗号分隔）' 用户手动清空：直接置空返回If Len(newVal) = 0 ThenTarget.Value = ""GoTo ExitHandlerEnd If' 原来没有任何内容：直接写入新值If Len(oldVal) = 0 ThenTarget.Value = newValGoTo ExitHandlerEnd If' 拆分旧值，去掉重复项items = Split(oldVal, ",")result = ""For i = LBound(items) To UBound(items)items(i) = Trim$(items(i))If Len(items(i)) > 0 _And StrComp(items(i), newVal, vbTextCompare) <> 0 _And InStr(1, "," & result & ",", "," & items(i) & ",", vbTextCompare) = 0 Thenresult = result & IIf(Len(result) = 0, "", ",") & items(i)End IfNext i' 若新值不在旧值里，则追加；若已存在，相当于反选，直接不追加If InStr(1, "," & oldVal & ",", "," & newVal & ",", vbTextCompare) = 0 Thenresult = result & IIf(Len(result) = 0, "", ",") & newValEnd IfTarget.Value = resultExitHandler:Application.EnableEvents = True
End Sub

这个脚本可以实现 选择，清除，反选 操作