Excel怎么下拉框多选

打开Exlce，

确定，然后

右击查看代码，把这段代码复制到新建的文件里面

此时Excel会给出提示，选择否，，系统会提示保存，在保存的时候选择启用宏的工作簿然后保存，此时Excel下拉框多选就搞定了，最后，代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Option Explicit   Sub Worksheet_Change(ByVal Target As Range) '让数据有效性选择 可以多选,重复选 Dim rngDV As Range Dim oldVal 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 If oldVal = "" Then Else If newVal = "" Then Else Target.Value = oldVal _ & ", " & newVal End If End If End If   exitHandler: Application.EnableEvents = True End Sub

优化后的代码

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

Option Explicit    Sub Worksheet_Change(ByVal Target As Range) '让数据有效性选择 可以多选,重复选 Dim rngDV As Range Dim oldVal 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 If oldVal = "" Then Else If newVal = "" Then Else If Target.Column <> 2 And Target.Column <> 3 And Target.Column <> 5 Then Dim oldValArray oldValArray = Split(oldVal, ",") Dim exitVal As Boolean exitVal = False Dim i As Integer Dim resultVal As String For i = 0 To UBound(oldValArray)   If oldValArray(i) = newVal Then     exitVal = True   Else     If resultVal = "" Then       resultVal = oldValArray(i)     Else       resultVal = resultVal & "," & oldValArray(i)     End If   End If Next If exitVal = False Then    If oldVal = newVal Then      Target.Value = resultVal    Else      Target.Value = resultVal & "," & newVal    End If Else Target.Value = resultVal End If End If End If End If End If    exitHandler: Application.EnableEvents = True End Sub

　　

　　　

转载自：https://www.cnblogs.com/boosasliulin/p/5970120.html