项目中需要对Execl的某一列设置多选,研究半天,各种百度,最后找到解决方案,按照如下进行设置就行。
1.复制下面全部代码;
Private Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''''''''''' begin of MultiSelect ''''''''''''''''''''''''''''''''''
' 允许excel内置的下拉框(数据校验形式实现的)多选
Dim colArr As Variant
Dim connector As String
Dim oldVal As String
Dim newVal As String
colArr = Array(1,3) ' 自定义第几列要多选,1就是A列,3就是C列,有需要继续加,注意代码里的逗号要用英文输入法
connector = "," ' 自定义多个选项之间的连接符,可改为想要的,若要用回车作为连接符,则直接用后面这句替代 connector= Chr(10)
If Target.Count > 1 Then GoTo exitHandler
On Error GoTo exitHandler
'SpecialCells being slow seems to be excel 2010+'s bug from the beginning. So MS.
If Target.Validation.Type <= 0 Or WorksheetFunction.Match(Target.Column, colArr, 0) <= 0 Then
If Err.Number <> 0 Then GoTo exitHandler
End If
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal = "" Or newVal = "" Then ' 若改之前或改制后单元格内容为空,则不执行代码
'do nothing
ElseIf InStr(1, newVal, connector) Then ' 若新内容内有连接符,说明不是通过下拉操作的,则不执行代码
'do nothing
ElseIf newVal = oldVal Then ' 只剩唯一一个选项且重复选择时,不执行代码
'do nothing
ElseIf InStr(1, oldVal, newVal) <> 0 Then ' 剩余多个时重复选择视同删除
If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then ' 最后一个是选项重复
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - Len(connector))
Else ' 非最后一个选项重复的时候处理逗号
Target.Value = Replace(oldVal, newVal & connector, "")
End If
Else ' 非重复选项就视同增加选项
Target.Value = oldVal & connector & newVal
End If
exitHandler:
Application.EnableEvents = True
'''''''''''''''''''''''''''''''''' end of MultiSelect ''''''''''''''''''''''''''''''''''
End Sub
2.在需要应用单选框的sheet页面的页面名称标签(底部)上右键并选择 查看代码;
3.在弹出的窗体内直接粘贴代码并保存即可;
4.如有需要可在开始几行自定义连接符和哪几列需要多选,代码内搜索“自定义”即可。