数据验证下拉框多选

暗夜央

数据验证设置现在只能做单选，如何设置可以实现多选，中间以顿号隔开，实现效果如图

guobaojin

这要求很奇葩呀！

暗夜央

guobaojin 发表于 2022-7-18 14:04
这要求很奇葩呀！

没办法，设备故障填表需要，差不多要40个设备了，如果枚举罗列太多了，想看下有没有能直接设置多选的

roserice

用list.view控件吧

暗夜央

现在找到一个多选的代码，但我原sheet下已经有一个Worksheet_Change，再加一个会导致第二个报错，有没有办法把两个代码整合到一个里

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim rngDV As Range
   
3. Dim oldVal As String
   
4. Dim newVal As String
   
5. If Target.Count > 1 Then GoTo exitHandler
   
6. 
   
7. On Error Resume Next
   
8. Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
   
9. On Error GoTo exitHandler
   
10. 
    
11. If rngDV Is Nothing Then GoTo exitHandler
    
12. 
    
13. If Intersect(Target, rngDV) Is Nothing Then
    
14.    'do nothing
    
15. Else
    
16.   Application.EnableEvents = False
    
17.   newVal = Target.Value
    
18.   Application.Undo
    
19.   oldVal = Target.Value
    
20.   Target.Value = newVal
    
21.   If Target.Column = 7 Then '????涨??????е???????Ч?????????A?????1?У????????????3????C?У?7????G??
    
22.     If oldVal = "" Then
    
23.       'do nothing
    
24.       Else
    
25.       If newVal = "" Then
    
26.       'do nothing
    
27.       Else
    
28.         If InStr(1, oldVal, newVal) <> 0 Then  '????????????
    
29.           If InStr(1, oldVal, newVal) + Len(newVal) - 1 = Len(oldVal) Then '????????????
    
30.             Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 1)
    
31.           Else
    
32.             Target.Value = Replace(oldVal, newVal & ",", "") '??????????????????????????
    
33.           End If
    
34.         Else '?????????????????????
    
35.         Target.Value = oldVal & "," & newVal
    
36. '      NOTE: you can use a line break,
    
37. '      instead of a comma
    
38. '      Target.Value = oldVal _
    
39. '        & Chr(10) & newVal
    
40.         End If
    
41.       End If
    
42.     End If
    
43.   End If
    
44. End If
    
45. 
    
46. exitHandler:
    
47.   Application.EnableEvents = True
    
48. End Sub
    
49. 
    

复制代码