excel下拉多选
Excel下拉框一般只能单选,但有时候需要多选,多选的方法如下:
以office 2016中的excel为例:
1、数据验证入口
2、设置数据
3、sheet页右击查看代码
4、复制下面代码进去:
源码地址: Excel Data Validation Drop Down Select Multiple Items (contextures.com)
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 = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing 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 exitHandler: Application.EnableEvents = True End Sub
5、效果如下: