excel下拉框多选

1. 鼠标右击下面的工作表（如sheet1）。选择“查看代码”，就可打开VBA编辑界面

2. 复制下面代码到VBA编辑界面，并保存即可

这样，我们的下拉菜单表就可以实现多选。

注意：

    a.excel已经启用宏

    b.excel的后缀名是.xlsm，是属于启用宏的excel表格

下拉框多选，并且再次选择就删除已存在项的功能

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)

'让数据有效性选择 可以多选,重复选

Dim rngDV As Range

Dim oldVal As String

Dim tVal As String

Dim newVal As String

If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then

'do nothing

Else

Application.EnableEvents = False

newVal = Target.Value

Application.Undo

oldVal = Target.Value

Target.Value = newVal

tVal = ""

If oldVal = "" Then

Else

    If newVal = "" Then

    Else

        If InStr(1, "," & oldVal & ",", "," & newVal & ",") <> 0 Then '已存在

            tVal = Replace(Replace(oldVal & ",", newVal & ",", ""), ",,", ",")

            If InStr(Len(tVal), tVal, ",") <> 0 Then '判断最后一位是否逗号，是则不要最后一位

                tVal = Left(tVal, Len(tVal) - 1)

            End If

            Target.Value = tVal

        Else

            Target.Value = oldVal & "," & newVal

        End If

    End If

End If

End If

exitHandler:

Application.EnableEvents = True

End Sub