1 实现单元格下拉框
来源需要用英文逗号分隔,这里输入限制为最大255字符,如果需要更多可选择单元格范围:
到这里就实现了下拉选择:
2 导入VBA脚本实现多选
按Alt+F11唤出VBA代码编辑器,选中你的sheet,复制代码然后保存,保存时选否,保存为.xlsm文件
代码如下:
Private Sub Worksheet_Change(ByVal Target As Range)
'UpdatebyExtendoffice20180510
Dim I As Integer
Dim xRgVal As Range
Dim xStrNew As String
Dim xStrOld As String
Dim xFlag As Boolean
Dim xArr
On Error Resume Next
Set xRgVal = Cells.SpecialCells(xlCellTypeAllValidation)
If (Target.Count > 1) Or (xRgVal Is Nothing) Then Exit Sub
If Intersect(Target, xRgVal) Is Nothing Then Exit Sub
Application.EnableEvents = False
xFlag = True
xStrNew = Target.Value
Application.Undo
xStrOld = Target.Value
If xStrNew <> "" Then
If InStr(1, xStrOld, xStrNew) = 0 Then
xStrNew = xStrNew & IIf(xStrOld <> "", "、" & xStrOld, "")
Else
xStrNew = xStrOld
End If
End If
Target.Value = xStrNew
Application.EnableEvents = True
End Sub
再看文件时已经可以多选了:
自定义分隔符号:
更改这个顿号为你想要的后保存脚本即可。
参考链接:https://www.5axxw.com/questions/content/wq9lhv