php excel复选框,excel如何实现下拉框复选

excel如何实现下拉框复选？

EXCEL选择下拉框实现复选

第一步：新建一个excel且设置数据有效性【选中X列--数据--有效性】

第二步：开发工具--查看代码--把代码复制进去保存就OK了

代码如下：Private Sub Worksheet_Change(ByVal Target As Range)

' Developed by Contextures Inc.

' www.contextures.com

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 Target.Column = 7 Then '这里规定好哪一列的数据有效性是多选的，A列是第1列，依次类推，如3就是C列，7就是G列

If oldVal = "" Then

'do nothing

Else

If newVal = "" Then

'do nothing

Else

If 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) - 1)

Else

Target.Value = Replace(oldVal, newVal & ",", "") '不是最后一个选项重复的时候处理逗号

End If

Else '不是重复选项就视同增加选项

Target.Value = oldVal & "," & newVal

' NOTE: you can use a line break,

' instead of a comma

' Target.Value = oldVal _

' & Chr(10) & newVal

End If

End If

End If

End If

End If

exitHandler:

Application.EnableEvents = True

End Sub

更多Excel相关技术文章，请访问Excel基础教程栏目！