作者:乔山办公网日期:
返回目录:excel表格制作
利用excel的数据有效性功能。百
1、打开excel表格后,选中目标表格,点击数据菜度单中的“数据有效性”图知标。
2、然后道将允许设置为“序列”,如图。
3、然后在来源栏输入选项内容,选项之间用英文的逗号隔开,进行确定。版
4、完成权以上设置后,即可实现excel下拉菜单多选。
这个用数据有知效性是没办法了,只能使道用VBA,在版文库中有具体方法,我就权不jjww了...
http://wenku.baidu.com/link?url=tbnGSI5O2NPYECWCDTGBpQPKvnW48FpJMkensTNrCB0yO40aMWfdROI7s5a53NcwNvqKhS6q12i5EY9WfQn3SWAKwnmUQl-4-swE2qLrbCW
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range, oldVal As String, newVal As String
If Target.CountLarge > 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 = 5 Or Target.Column = 7 Or Target.Column = 9 Or Target.Column = 11 Then '这里规定好哪一列的数据有效性是多选的,A列是第1列,依次类推,如3就是C列,7就是G列
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
If newVal = "全选7a686964616fe59b9ee7ad94334" Then '当新选择的值是全选时,目标值为全选
Target.Value = "全选"
Else
If InStr(1, oldVal, "全选") <> 0 Then '当目标值中有全选时,选择新的非全选值时为新的值
Target.Value = newVal
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
End If
End If
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
使用VBA代码是可以实现复选的功能!